Summary
SQL-heavy technical screen for a data scientist role at SIG. Six questions back to back, all analytics SQL, no behavioral fluff. The problems ranged from basic conversion funnels to some genuinely tricky window function and cross-join completeness stuff.
Questions Asked(6)
Straightforward on the surface but the 'regardless of when delivered' part tripped me up briefly.
Suggested Approach
Start by clearly defining the 7-day window filter on the 'created_at' timestamp to scope the order cohort, then aggregate delivered orders (status = 'delivered' AND delivered_at IS NOT NULL) grouped by city and courier_type. Express the conversion rate as COUNT(delivered orders) / COUNT(total created orders) per group, handling potential division-by-zero edge cases.
Define the Time Window
Clarify the 7-day window by filtering orders where created_at falls within a specific date range (e.g., BETWEEN '2024-01-01' AND '2024-01-07'). Confirm whether the window is fixed historically or rolling, and use inclusive/exclusive bounds consistently.
Identify Delivered Orders
Within the scoped cohort, flag an order as delivered only when status = 'delivered' AND delivered_at IS NOT NULL. Note that the delivery event can occur outside the 7-day window — what matters is that the order was created within it.
Aggregate by City and Courier Type
Group the filtered dataset by city and courier_type, computing COUNT(*) for total created orders and SUM of the delivered flag for delivered orders. This gives the numerator and denominator needed for the conversion rate.
Compute Conversion Rate Safely
Calculate conversion_rate = delivered_count / NULLIF(created_count, 0) to avoid division-by-zero errors. Optionally multiply by 100 and round to a meaningful decimal precision for readability.
Validate and Interpret Results
Sanity-check the output by verifying that conversion rates fall between 0 and 1 (or 0–100%), and flag any city/courier_type combinations with very low order volumes where rates may be statistically unreliable. Consider adding a volume threshold or confidence note.
Key Points to Mention
The zero-fill requirement is where people mess up.
Suggested Approach
Start by generating a complete spine of all (month, courier_type) combinations for the last 3 months using a cross join, then left-join actual order data onto that spine so zero-activity months are preserved. Aggregate delivered counts and created counts separately, then compute GMV as delivered_count multiplied by the fixed rate per courier type.
Define the Date Spine
Generate a reference table of the last 3 calendar months (e.g., using DATE_TRUNC + GENERATE_SERIES or a CTE with explicit month values) to ensure every month appears in the output regardless of activity.
Enumerate All Courier Types
Pull the distinct list of courier types from the reference/dimension table (or from the orders table itself) so you can cross join it with the date spine to create every (month, courier_type) combination.
Aggregate Order Metrics
Left-join the orders table onto the spine, grouping by month and courier type to compute COUNT of orders created and COUNT of orders with a delivered status, using COALESCE to replace NULLs with zero.
Calculate GMV
Multiply the delivered count by the fixed rate per courier type (either a constant or looked up from a rates table) to derive GMV, ensuring the rate is applied after the COALESCE so zero-activity rows yield GMV of 0.
Format and Validate Output
Order results by month and courier type, then sanity-check that every expected (month, courier_type) row is present and that aggregate totals align with known benchmarks or a simpler rollup query.
Key Points to Mention
This was the one that made me sweat.
Suggested Approach
Filter out non-delivered orders first, then use a window function to assign row numbers partitioned by user and ordered by time, resetting the sequence whenever a non-biker delivery is encountered. Identify consecutive biker-only streaks of length ≥ 3 and extract the first such streak per user, returning the 1st and 3rd order IDs along with the timestamp difference in minutes.
Filter & Rank Delivered Orders
Remove all non-delivered orders from the dataset entirely, as they are irrelevant to streak calculation. Assign a row number (e.g., delivered_rank) partitioned by user_id and ordered by order timestamp to establish a clean sequence of delivered orders.
Flag Biker vs. Non-Biker Deliveries
Within the filtered delivered orders, add a boolean flag indicating whether each order was delivered by a biker. Any non-biker delivery resets the streak, so this flag is the key signal for grouping.
Assign Streak Groups
Use the classic 'gaps and islands' technique: compute delivered_rank minus a row number partitioned by user_id and biker_flag to create a group identifier. Rows with the same group ID and biker_flag = true form a consecutive biker streak.
Identify Valid Streaks of Length ≥ 3
Count the size of each streak group and filter to those with at least 3 orders. Within each qualifying streak, use ROW_NUMBER() to label positions 1, 2, 3... so you can pinpoint the 1st and 3rd orders.
Extract First Streak Per User & Compute Time Span
Rank the valid streaks per user by the timestamp of their first order and keep only rank = 1 (the earliest streak). Return the order_id of position 1, the order_id of position 3, and DATEDIFF or TIMESTAMPDIFF in minutes between their timestamps.
Key Points to Mention
Pretty standard once you know percentile_cont.
Suggested Approach
Approach this as a SQL aggregation problem requiring window functions or percentile functions, filtering for delivered orders within the last 30 days and grouping by courier type. Use a database-appropriate percentile function (e.g., PERCENTILE_CONT in standard SQL or APPROX_PERCENTILE in distributed systems) to compute the 95th percentile of delivery duration in minutes. Clearly define delivery time as the difference between delivery timestamp and order creation timestamp.
Clarify the Schema & Definitions
Identify the relevant table(s) and columns: order creation timestamp, delivery timestamp, order status, and courier type. Confirm that 'delivery time' means the elapsed minutes between order creation and delivery completion.
Filter the Dataset
Apply WHERE conditions to restrict rows to orders with status = 'delivered' and where the order creation timestamp falls within the last 30 days (e.g., created_at >= CURRENT_DATE - INTERVAL '30 days').
Compute Delivery Duration
Calculate delivery time in minutes using timestamp arithmetic, such as EXTRACT(EPOCH FROM (delivered_at - created_at)) / 60 in PostgreSQL or DATEDIFF('minute', created_at, delivered_at) in other dialects.
Apply Percentile & Count Aggregations
Group by courier_type and apply PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY delivery_minutes) for the 95th percentile, along with COUNT(*) for the group size.
Validate & Interpret Results
Check for NULL delivery timestamps or negative durations that could indicate data quality issues, and briefly interpret what a high 95th percentile for a specific courier type would mean operationally.
Key Points to Mention
Harder version of task B.
Suggested Approach
Start by identifying all distinct cities from both the users and couriers tables using a UNION to create a complete city dimension, then cross join it with all courier types and all months to build a complete spine of combinations. Finally, left join the actual order data onto this spine so that missing combinations naturally appear as zero rather than being omitted.
Build the Complete City Dimension
Use UNION (not UNION ALL) on the city column from both the users and couriers tables to get every distinct city that appears in either source, eliminating duplicates.
Generate the Full Spine via CROSS JOIN
Cross join the city dimension with all distinct courier types and all relevant months (e.g., from a date dimension or derived from the orders table) to produce every possible combination as the reporting spine.
Aggregate Actual Order Data
Separately aggregate the orders table by city, courier type, and month to get real volume counts, keeping this as a subquery or CTE before joining.
Left Join Actuals onto the Spine
Left join the aggregated order data onto the full spine using city, courier type, and month as join keys, then use COALESCE to replace NULL order counts with zero.
Validate and Sanity-Check Output
Verify the total row count equals cities × courier_types × months, and confirm that the sum of the new report's volume matches the original report's total to ensure no data was lost or duplicated.
Key Points to Mention
Easy one to end on.
Suggested Approach
Approach this as a filtering and derived-column problem: first scope the dataset to orders at or before the cutoff timestamp, then compute the delivery duration and apply the two-hour threshold logic. Handle NULL delivered_at values explicitly using COALESCE or IS NULL checks, and surface the result as a boolean flag column for downstream use.
Clarify the Cutoff and Schema
Confirm the data types of created_at and delivered_at (TIMESTAMP vs. DATETIME) and how the cutoff value is supplied — as a parameter, a variable, or a hardcoded literal. This prevents silent type-mismatch errors.
Filter Orders Up to the Cutoff
Use a WHERE clause with created_at <= :cutoff_timestamp to scope the result set before applying any delivery logic, keeping the query efficient and the intent clear.
Compute Delivery Duration
Calculate the elapsed time between created_at and delivered_at using TIMESTAMPDIFF (MySQL), EXTRACT(EPOCH FROM ...) (PostgreSQL), or DATEDIFF with appropriate units, depending on the SQL dialect in use.
Apply the Stale-Order Condition
Flag an order as stale when delivered_at IS NULL OR the computed duration exceeds 7200 seconds (2 hours). Use a CASE WHEN expression or a boolean cast to produce the flag column, e.g., CASE WHEN delivered_at IS NULL OR delivery_seconds > 7200 THEN TRUE ELSE FALSE END AS is_stale.
Validate and Discuss Extensions
Sanity-check the output by checking counts of stale vs. non-stale orders and verifying a few NULL rows manually. Mention how this flag could feed into a root-cause analysis — e.g., joining with courier or region tables to identify systemic delays.
Key Points to Mention
Discussion(5)
Sign in to join the discussion.
The 'regardless of when delivered' phrasing is doing a lot of work there and it's easy to blow past it. Your fix is right: anchor the window filter entirely on created_at, then use CASE WHEN status = 'delivered' AND delivered_at IS NOT NULL THEN 1 ELSE 0 END inside a SUM (or a COUNT with a FILTER clause if you're on Postgres) to get delivered count. The denominator is just COUNT(*) of all orders in that created_at window. I've seen people write this with a WHERE on status and then wonder why their denominator is wrong, which is a subtler version of the same mistake you almost made. Fast reading kills you on these.
Yeah, this is a relief question after Q3. The boolean column is just (delivered_at IS NULL OR delivered_at > created_at + INTERVAL '2 hours') AS is_stale, filter the whole thing on created_at <= your cutoff, and you're done. The only micro-gotcha is making sure you're not accidentally excluding undelivered orders with a status filter somewhere upstream.
Gap-and-islands with a filtered subset is rough, and doing it live under a SIG phone screen sounds painful. Your approach is the right one though. Number all delivered orders per user chronologically (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY delivered_at)), then separately number only the delivered biker orders per user the same way. Subtract the biker-specific row number from the overall delivered row number and you get a constant group key for each consecutive biker run. A non-biker delivered order increments the overall counter but not the biker counter, so the difference shifts and breaks the group. To isolate the first streak, you MIN() the group key per user and filter to that. Then within that group you grab the row where biker_rn = 1 and biker_rn = 3 for your order IDs, and DATEDIFF or EXTRACT(EPOCH ...) between those two timestamps for the minutes span. Messy to type out fast but the logic holds.
This one is legitimately harder than Q2 because the city dimension doesn't live on the order itself, which creates a join sequencing problem that's easy to get tangled in. The spine needs three things crossed together: months, courier types, and cities. For cities, you want a UNION (not UNION ALL) of the city column from users and couriers so you catch every city that appears anywhere in the data. Then CROSS JOIN that cities list with your courier types list and your calendar months. That's your full spine. To get actual order data onto it, join orders to couriers on courier_id to pull in the courier's city, then aggregate, then LEFT JOIN that aggregated result onto the spine. The key mistake you described, joining city in the wrong direction, usually happens when you try to join the spine to orders directly and end up filtering out spine rows that have no matching orders. The aggregation has to happen first, then the left join to the spine, not the other way around.
Zero-fill requirements are genuinely where SQL screens separate people, and the cross join piece is the part that's easy to forget under pressure. The structure I'd reach for: generate your 3-month calendar spine with something like a recursive CTE or a VALUES list of the three month-start dates, then CROSS JOIN that with SELECT DISTINCT courier_type FROM couriers to get every combination you need to preserve. From there it's a LEFT JOIN onto your aggregated orders, with COALESCE wrapping the counts and GMV to turn NULLs into zeros. The GMV calc is almost an afterthought once the spine is solid. One thing worth double-checking: make sure your month truncation (DATE_TRUNC('month', created_at)) in the orders aggregation matches exactly how you're labeling months in your spine, otherwise the join silently drops rows and you get gaps again without realizing why.