The two-denominator thing tripped me up more than I want to admit.
Start by clarifying the schema and the exact definitions of the 7-day window, exposure, and the compound filter. Then outline a SQL solution using a CTE to join orders with exposures, apply the filter, and compute the two percentages via conditional aggregation. Finally, discuss how to validate the results and interpret the difference between the two metrics.
Pro tip: Always confirm whether the 7-day window is rolling or fixed, and whether exposure is assigned at the unit level or order level; this ambiguity is a common trap in experimentation questions. Also, mention that the two denominators answer different questions: the exposed-only denominator measures treatment effect, while the all-orders denominator measures overall impact.
Ask about the table schemas, the definition of a 7-day window (e.g., rolling vs. fixed), how exposure is determined (e.g., unit-level), and whether the filter applies to all orders or only those in the window. Confirm that 'biker courier' and 'cold temp category' are fields in the orders table.
Plan to use a CTE to join orders with exposures on unit_id and date, filter orders to the 7-day window, and apply the compound filter. Then compute the two percentages using conditional aggregation: one with a condition on exposure status, and one without.
Use SUM(CASE WHEN ... THEN 1 ELSE 0 END) / COUNT(*) for the all-orders denominator, and SUM(CASE WHEN exposed = 1 AND ... THEN 1 ELSE 0 END) / SUM(CASE WHEN exposed = 1 THEN 1 ELSE 0 END) for the exposed-only denominator. Ensure the filter conditions are correctly applied.
Check for edge cases like missing exposure data or orders outside the window. Compare the two percentages to understand the experiment's impact: the exposed-only metric shows the treatment effect, while the all-orders metric shows the overall population impact.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
This one requires aggregating before you can even define the boolean, so you can't just filter rows like in the first problem.
First, clarify the two denominator conventions (e.g., all unit-days vs. unit-days with at least one order) and confirm the definition of 'biker-cold' orders. Then, for each unit-date, compute the trailing 7-day count of biker-cold orders and their average subtotal, flag power days, and calculate the percentage under each denominator.
Pro tip: When defining trailing windows, ensure you use the correct date range (e.g., the 7 days prior to and including the current date) and handle edge cases like missing dates or units with no orders. Also, consider whether the average subtotal should be computed only on biker-cold orders or all orders in the window.
Confirm what 'biker-cold' means (likely orders delivered by bikers and cold, but verify with stakeholders) and specify the two denominator conventions (e.g., all unit-days vs. unit-days with at least one order).
Filter orders to biker-cold ones, ensure each order has a unit, date, and subtotal, and create a complete date spine for each unit to account for days with no orders.
For each unit-date, calculate the number of biker-cold orders in the trailing 7 days and the average subtotal of those orders (if any).
Mark a unit-date as a power day if the count >= 2 and average subtotal >= 2000 cents. Then compute the percentage of power days under each denominator convention.
Check for anomalies (e.g., units with sparse data) and interpret the percentages in the context of the business question, noting any caveats.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
The zero-order days requirement meant I had to generate a date spine and cross join it with distinct unit_ids, then left join orders onto that.
Start by clarifying the exact window frame definitions for each metric, then outline a SQL-based solution that builds a complete date-unit grid using a cross join or calendar table. Use window functions with explicit ROWS/RANGE clauses to compute cumulative, rolling, and non-overlapping changes, and validate results with edge cases like zero-order days.
Pro tip: Explicitly state the window frame (e.g., ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) for each metric, as interviewers often test whether you understand the difference between ROWS and RANGE and how they affect rolling calculations.
Ask about the exact window frame definitions for each metric (e.g., cumulative orders: unbounded preceding to current row; 7-day rolling sum: 6 preceding to current row; non-overlapping 7-day percent change: current row vs. 7 rows prior). Confirm the date range and unit granularity.
Generate a cross join of all distinct units and all dates in the 7-day window (using a calendar table or recursive CTE), then left join the orders table to fill in zero-order days.
Use window functions: SUM(orders) OVER (PARTITION BY unit ORDER BY date) for cumulative, and SUM(orders) OVER (PARTITION BY unit ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) for the 7-day rolling sum.
For day-over-day percent change, use LAG(orders, 1) and compute (current - previous) / previous. For non-overlapping 7-day percent change, use LAG(orders, 7) and compute (current - lag7) / lag7, ensuring the window frame is correctly defined.
Check for division by zero, nulls, and ensure the grid includes all units and dates. Validate results by manually computing a few rows and discussing how to handle incomplete windows (e.g., first few days).
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Break the problem into two phases: first, compute the daily boolean flag per unit and detect flips using a window function (LAG) to compare consecutive days; second, for each flip, calculate the 7-day rolling order count before and after the flip date, compute the delta, and then rank these deltas across all flips using a percent rank function. Ensure the rolling window is correctly defined (e.g., 7 days prior to the flip vs. 7 days after) and handle edge cases like missing dates or units with no flips.
Pro tip: When computing the 7-day rolling counts, use a window that includes the flip date in the 'after' period and excludes it from the 'before' period to avoid double-counting; also, consider normalizing by unit volume or using percent rank to compare across units with different scales.
For each unit and date, determine whether a qualifying high-value biker-cold order exists (flag = 1 if yes, else 0). Use LAG to compare the flag with the previous day's flag; a flip occurs when the flag changes (0→1 or 1→0).
For each flip date, calculate the sum of orders over the 7 days before the flip (excluding the flip date) and the 7 days after (including the flip date). This gives two rolling counts per flip.
Compute the difference between the after and before rolling counts (delta = after - before). The direction of change is determined by the flag transition: 'up' for 0→1, 'down' for 1→0.
Use a percent rank function (e.g., PERCENT_RANK() in SQL) over the delta values across all flip events to determine the relative position of each delta. This helps identify flips with unusually large or small changes.
Return unit, flip date, direction, delta, and percent rank. Validate by checking for missing dates, ensuring rolling windows are correctly aligned, and confirming that percent ranks are between 0 and 1.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.