The Vote Tally

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

L5

Problem

The transactions table records one row per sale, with the amount charged and the day it was made. Give the total taken in on each day of September 2026, earliest day first.

One product, every day it sold, and the money it brought in.

Tables

transactions

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

products

Columns: product_id INTEGER, product_name TEXT, category TEXT, price REAL, rating REAL, in_stock INTEGER

Expected output

Worked solution and explanation

What this really is

Under the finance costume, this question is about grain. The table has one row per sale, and the answer needs one row per day, so what you are really being asked is whether you notice those are different. In September 2026 the 3rd and the 7th each have two sales and the 6th has three. A query that just lists the month's sales returns 12 lines with repeated dates, and each line shows one sale instead of the day's takings. The other bait is the products table sitting next to transactions: nothing in it belongs in the answer.

Building it

Step 1: Scope to the month

Keep rows with transaction_date >= '2026-09-01'. The dates are ISO strings, so comparing them as text is the same as comparing them as dates. Nothing in the table is dated after today, so the lower bound alone covers the whole month to date.

Step 2: Fold each day into a single figure

Use GROUP BY transaction_date with SUM(total_amount). On the 6th, three sales of 238.98, 1,208.82 and 616.14 become one 2,063.94, and the 3rd and the 7th each collapse from two lines to one. That folding is the whole answer.

Step 3: Lay the days out in calendar order

Finish with ORDER BY transaction_date. The result is read as a timeline, so the order is part of the answer.

SELECT
    transaction_date,
    SUM(total_amount) AS daily_revenue
FROM transactions
WHERE transaction_date >= '2026-09-01'
GROUP BY transaction_date
ORDER BY transaction_date;

Raw sales (wrong)

Selecting transaction_date and total_amount for the month returns one line per sale. The 6th shows up three times, and the 3rd and the 7th twice each. Someone still has to add them up by hand.

Daily totals (right)

Adding GROUP BY transaction_date and SUM(total_amount) folds those lines into nine rows, one per day, so you can read the column straight down.

Common follow-up questions

• Now add a running total across the days of the month. (Tests whether they can layer a cumulative window over the grouped result.)
• Show the 10th through today with 0 revenue too. (Needs a date spine, because `transactions` only has days with activity.)
• Break it out per product, one row per product per day. (Checks that `product_id` goes into both the grouping and the output, and that they decide what to do with sales whose `product_id` is `NULL`.)