Peak Season

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

Lyft

Problem

Finance's peak-season forecast leans on a single region and calendar month as the profit high point, and nobody has confirmed which pairing actually earns that spot. Tie each row in orders to every row in transactions whose ID falls in the same bucket modulo 100, total the order profit by region and the transaction's calendar month, and return the top pairing with its total.

One region, one month. Peak profit.

Tables

orders

Columns: order_id INTEGER, status TEXT, region TEXT, profit REAL

transactions

Columns: transaction_id INTEGER, user_id INTEGER, product_id INTEGER, quantity INTEGER, total_amount REAL, transaction_date TEXT

Expected output

Worked solution and explanation

What this is really asking

o.order_id % 100 = t.transaction_id % 100 is not a real key. It is a synthetic bucket join across 30M and 100M rows that fans into roughly a 30-trillion-row pre-aggregate. Every shape decision flows from that.

Break down the requirements

Step 1: Join on the modulo bucket

Match each order to every transaction sharing the same (id mod 100). This is a many-to-many bucket join, not a referential lookup.

Step 2: Group by region and transaction month

Pull the calendar month out of transaction_date with strftime('%m', t.transaction_date) so the same month across years collapses together, then SUM profit per (region, month). The result is a two-digit string like '05', which is exactly what the grader expects.

Step 3: Order by total_profit and LIMIT 1

The prompt asks for one winner. Sort descending on the aggregate and take the top row. Ties resolve arbitrarily without a tiebreaker column.

The solution

SELECT
    o.region,
    strftime('%m', t.transaction_date) AS txn_month,
    SUM(o.profit) AS total_profit
FROM orders o
INNER JOIN transactions t
    ON (o.order_id % 100) = (t.transaction_id % 100)
GROUP BY o.region, strftime('%m', t.transaction_date)
ORDER BY total_profit DESC
LIMIT 1

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

• How would you break a tie if two region-months had identical `total_profit`? (Add a deterministic secondary key like region ASC or the count of contributing orders to the ORDER BY.)
• How would you rewrite this so the optimizer can actually use an index? (Persist `order_id % 100` as a generated column with an index, or pre-bucket both tables into a join-helper table.)
• What if finance wants the top region-month per year, not overall? (Group by region, year, and month, then use RANK() OVER (PARTITION BY year ORDER BY SUM(profit) DESC) and filter rnk = 1.)