Status Report

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

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Tata Consultancy Services

Problem

Our per-status profit report has been counting orders that were never tagged to a sales region. Rebuild it from the orders table so each status shows its order volume and average profit, keeping only statuses backed by at least 24 properly tagged orders, highest average profit first.

Where are orders getting stuck?

Tables

orders

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

Expected output

Worked solution and explanation

What this report is really testing

This is a two-stage filter in an ops costume: one condition judges a single order, the other judges a whole status, and the skill is putting each in the stage that can actually evaluate it. Anyone can tally orders per status and average the profit. What separates candidates is seeing that 'tagged to a sales region' is a fact about one row, while 'backed by at least 24 orders' is a fact about a group that does not exist until the tally is done. Skip the region exclusion and 'Processing' climbs from 22 to 26 orders and clears a bar it should fail. Put the cutoff next to the region check instead, and the query has no count to compare against.

Walk it through

Step 1: Drop the unattributed orders

An order with a NULL region was never attributed to a market, so WHERE region IS NOT NULL removes it before anything is tallied. On this data that matters: it lowers several status counts, and it is what takes 'Processing' from 26 orders down to 22.

Step 2: Tally and average per status

GROUP BY status collapses the remaining orders into one row per status. A NULL status forms a group too, so the 14 orders with no status become their own bucket. COUNT(*) gives the volume and AVG(profit) the typical profit. Refunded orders with negative or zero profit stay in the average because they are real outcomes.

Step 3: Apply the cutoff after the tally

The 24-order bar is a count, and a count only exists once the groups are built, so the bar goes in HAVING. Here it drops 'Processing' (22) and the no-status bucket (14). Every status that survives has 24 or 26 orders.

Step 4: Sort by avg_profit

The report reads most profitable first, which here is also the only figure that separates the survivors: five of the six sit at exactly 24 orders. ORDER BY avg_profit DESC puts 'Completed' on top and 'Pending' at the bottom.

The solution

SELECT status, COUNT(*) AS order_count, AVG(profit) AS avg_profit
FROM orders
WHERE region IS NOT NULL
GROUP BY status
HAVING COUNT(*) >= 24
ORDER BY avg_profit DESC

One filter for everything

The engine rejects COUNT(*) >= 24 placed beside region IS NOT NULL in WHERE, because no aggregate exists while rows are still being scanned. Dropping the region check instead runs cleanly and quietly returns 'Processing' with 26 orders.

Row filter, then group filter

WHERE region IS NOT NULL trims rows during the scan, GROUP BY status builds the tallies, and HAVING COUNT(*) >= 24 trims whole statuses. Each condition sits in the only stage that can see what it tests.

Common follow-up questions

• The bar is a fixed 24 orders. How would you change the query if the bar became the median status volume instead? (Moves from a literal `HAVING` threshold to a computed one.)
• `profit` is negative for refunded orders. How does `AVG(profit)` treat them, and what would you report alongside the average to make it trustworthy? (Tests how aggregates handle signed values and why a supporting count matters.)
• If the business wanted unattributed orders shown as their own 'Unassigned' line instead of dropped, what changes? (Turns an exclusion into a labeled bucket, for example with `COALESCE(region, 'Unassigned')`.)