# The Ones Who Carry Us > Every quarter, a few names hold up the whole line. Name them. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 ## Problem The general manager is preparing the annual product performance review to see which products carry the top line. For each product that generated at least one hundred dollars, report its total revenue and units sold, biggest earner first. ## Worked solution and explanation ### What this problem really is Strip away the annual-review framing and this is a labeled aggregation with a threshold. You are summing revenue per product, attaching the product's name for readability, and keeping only the products whose total clears one hundred dollars. Anyone can write the join. The tell is where the threshold goes: a filter on the summed revenue has to run after the rows are grouped, which means HAVING, not WHERE. Reach for WHERE on the sum and you get an error; filter individual transactions above one hundred instead of the product's total and you quietly report the wrong leaderboard. --- ### Break down the requirements #### Step 1: Join `products` to `transactions` The join lines up each sale with its catalog row on the shared `product_id`. That is what lets the summed revenue sit next to a human-readable `product_name` in the output. #### Step 2: Aggregate by `p.product_name` `GROUP BY p.product_name` collapses every sale of a product into one row, and `SUM(t.total_amount)` plus `SUM(t.quantity)` compute the revenue and units for that product. #### Step 3: Filter groups with HAVING The one hundred dollar cut is a condition on the summed revenue, so it belongs in HAVING. WHERE cannot see the sum because it is evaluated before grouping happens. #### Step 4: Sort the final output `ORDER BY total_revenue DESC` puts the biggest earners on top, matching how the general manager reads the review from best to worst. --- ### The solution **Revenue and units per product with a post-aggregation filter** ```sql SELECT p.product_name, SUM(t.total_amount) AS total_revenue, SUM(t.quantity) AS units_sold FROM products p JOIN transactions t ON p.product_id = t.product_id GROUP BY p.product_name HAVING SUM(t.total_amount) >= 100 ORDER BY total_revenue DESC ``` > **Cost Analysis** > > With ~5M transaction rows, the aggregation is the dominant cost: the engine scans the sales, hash-aggregates by product, and only the small grouped result is filtered and sorted. An index on `transactions.product_id` turns the join probe from a scan into a seek against the 6K-row catalog. > **Interviewers Watch For** > > The single clearest signal is putting the revenue threshold in HAVING rather than WHERE. It tells the interviewer you know the difference between filtering rows and filtering groups. > **Common Pitfall** > > Writing HAVING total_amount >= 100 (or the same test in WHERE) filters individual sales, not products. Products whose single sales are small but whose totals cross the line get dropped, and the leaderboard is silently wrong. --- ## Common follow-up questions - About 5% of `transactions.product_id` values are NULL, and your inner join drops those sales entirely. Is excluding that revenue correct, or should the business see an 'unattributed' bucket? _(Tests whether the candidate notices that an inner join silently discards rows with a null join key.)_ - With 5,000,000 transactions, how would an index on `transactions.product_id` change the join and aggregation plan versus a full scan? _(Probes how indexing the join and grouping columns changes the execution plan at scale.)_ - If new transactions keep arriving, how would you maintain per-product revenue totals incrementally instead of re-aggregating the whole table each run? _(Tests understanding of incremental aggregation patterns for late-arriving data.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_ones_who_carry_us) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.