# The Vote Tally > One product, every day it sold, and the money it brought in. Canonical URL: Domain: SQL · Difficulty: hard · Seniority: L5 ## Problem Finance is reconstructing the daily revenue for product 1001. Show the total taken in on each day the product sold, earliest day first. ## Worked solution and explanation ### What this really is Strip off the finance costume and this is a filtered per-day sum over a single table. The whole skill being probed is restraint: there is a products table sitting right next to transactions, baiting you to join it, and a date column that can hold several sales on the same day, baiting you to hand back raw rows. Everything you need lives on transactions alone. Miss that a day can repeat and your output shows 2026-02-09 twice with half the money on each line, which is exactly the reconciliation error Finance is trying to kill. > **The one move that cracks it** > > product_id lives directly on transactions, so the filter and the metric both come from one table. Collapse each date with SUM so a day with three sales becomes one number, not three. ### Building it #### Step 1: Scope to the one product on the table that already has it WHERE product_id = 1001 on transactions. Do not reach for the products table to get there. The id you are filtering on is a column on transactions itself, so a join adds a scan and buys you nothing. #### Step 2: Collapse each day into a single figure GROUP BY transaction_date, then SUM(total_amount). In the seed, 2026-02-02 has two sales (35.04 and 17.52) and 2026-02-09 has two (70.08 and 17.52). Grouping turns each day into one row; the SUM is what makes 52.56 and 87.60 appear instead of four separate transaction lines. #### Step 3: Put the days in calendar order ORDER BY transaction_date. Finance is reading this as a timeline, so chronological order is part of the answer, not a nicety. Sorting the dates as stored strings works here because they are ISO formatted. **Daily net revenue for product 1001** ```sql SELECT transaction_date, SUM(total_amount) AS net_revenue FROM transactions WHERE product_id = 1001 GROUP BY transaction_date ORDER BY transaction_date; ``` *One table, one filter, one grouped sum, ordered by day.* **Raw rows (wrong)** SELECT transaction_date, total_amount FROM transactions WHERE product_id = 1001. This returns one line per sale, so 2026-02-02 shows up twice (35.04 and 17.52). Finance now has to add them by hand, which defeats the point. **Grouped sum (right)** Adding GROUP BY transaction_date and SUM(total_amount) folds those two sales into a single 52.56 line. One row per day, ready to read straight down the column. > **The join that costs you the question** > > The instinct on any two-table schema is to join. Here the products table has nothing the answer needs: no name, no category, no price enters the output, and product_id is already on transactions. An unnecessary inner join is a silent tell that you did not check where the columns live. > **What separates the levels** > > A junior often returns un-aggregated rows and calls it done because the small seed looks plausible. The signal of seniority is anticipating the repeat-day case before seeing it and reaching for GROUP BY the moment the grain is 'per day' rather than 'per transaction'. > **Why this stays cheap at scale** > > This is a single sequential pass over transactions with an aggregation, no join and no subquery. With an index on product_id (or a composite on product_id, transaction_date) the engine touches only the matching rows and can stream the grouped aggregate, so the plan stays flat even at hundreds of millions of transactions. ## Common follow-up questions - Now show the running total of net revenue across days, not just the per-day figure. _(Tests whether they can layer a window function over the grouped result.)_ - Include days in the window where the product had zero sales, showing 0 revenue. _(Forces a calendar or date-spine source, since transactions only carries days that actually had activity.)_ - Extend it to every product at once, one row per product per day. _(Checks that they add product_id to both the SELECT and the GROUP BY rather than looping per product.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_vote_tally) - [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.