Engineering Interview Practice

SQL interview questions with a worked join example

Practice SQL interview questions with a complete SQLite dataset, a faulty join, corrected aggregates and checks for duplicate totals and missing customers.

Three wooden trays of blank index cards beside a blue magnifying glass.
On this page
  1. Define the required customer-level result
  2. Load the complete SQLite fixture
  3. Trace the query that double-counts orders
  4. Aggregate item quantities before joining
  5. Explain the count, empty sums and DISTINCT trap
  6. Check four independent changes
  7. Frequently asked questions
  8. Sources

Work through SQL interview questions about joins and aggregates by defining what one output row represents. This original SQLite exercise requires one row per customer, including customers without paid orders. Reduce item quantities to one row per order before joining them to order amounts, then calculate customer totals.

Use this narrow database exercise as one part of your technical interview preparation. The data and question are fictional practice material, not an employer’s question or evidence of question frequency.

Define the required customer-level result

Return every customer with three values: the number of paid orders, their total amount in integer cents, and the sum of item quantities belonging to those paid orders. Sort the output by customer ID.

The exercise has these additional rules:

  • Only orders whose status is exactly paid qualify.
  • Customers with no orders or only pending orders remain, with zero totals.
  • A paid order without item rows still counts as an order and contributes its amount. Its item-unit contribution is zero.
  • Amount belongs to an order; quantity belongs to an item row. Do not multiply an order amount by item quantity.
  • Use the supplied integer inputs. Amounts are nonnegative and quantities positive. Dates, refunds and currency conversion are outside this exercise.

The required output is:

ID Customer Paid orders Paid amount, cents Item units
1 Ada 2 2000 7
2 Bo 0 0 0
3 Cy 0 0 0
4 Dee 1 0 0

Here, the grain means what one row represents. The result has customer grain. Orders and item rows begin at different grains, which is why joining them carelessly changes the totals.

Load the complete SQLite fixture

Start a fresh SQLite database, such as an in-memory session opened with sqlite3 :memory:. Run this setup block once, then run both queries below in the same session. Four customers, five orders and five item rows supply the entire exercise.

PRAGMA foreign_keys = ON;

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name TEXT NOT NULL
);
CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
  status TEXT NOT NULL CHECK (status IN ('paid', 'pending')),
  amount_cents INTEGER NOT NULL CHECK (amount_cents >= 0)
);
CREATE TABLE order_items (
  item_id INTEGER PRIMARY KEY,
  order_id INTEGER NOT NULL REFERENCES orders(order_id),
  quantity INTEGER NOT NULL CHECK (quantity > 0)
);

INSERT INTO customers VALUES
  (1, 'Ada'), (2, 'Bo'), (3, 'Cy'), (4, 'Dee');
INSERT INTO orders VALUES
  (101, 1, 'paid', 1000),
  (102, 1, 'paid', 1000),
  (103, 1, 'pending', 9000),
  (201, 2, 'pending', 500),
  (401, 4, 'paid', 0);
INSERT INTO order_items VALUES
  (1001, 101, 2),
  (1002, 101, 1),
  (1003, 102, 4),
  (1004, 103, 2),
  (2001, 201, 1);

Order 101 has two item rows, while order 102 has one. Both paid orders belong to Ada and have the same amount. Bo has only a pending order, Cy has none, and Dee’s paid order has no items.

The column declarations and checks support this supplied fixture; they are not a general input-validation policy. SQLite’s type behavior and a production data model need separate consideration.

Trace the query that double-counts orders

This intentionally wrong query joins the raw item rows and filters paid orders afterward:

SELECT c.customer_id, c.customer_name,
       COUNT(o.order_id) AS paid_orders,
       SUM(o.amount_cents) AS paid_amount_cents,
       COALESCE(SUM(i.quantity), 0) AS item_units
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
LEFT JOIN order_items AS i ON i.order_id = o.order_id
WHERE o.status = 'paid'
GROUP BY c.customer_id, c.customer_name
ORDER BY c.customer_id;

Its output is:

ID Customer Paid orders Paid amount, cents Item units
1 Ada 3 3000 7
4 Dee 1 0 0

Ada’s paid order 101 appears in two joined rows, one for each item. Order 102 appears once. The aggregate therefore counts three order IDs and adds 1000 + 1000 + 1000. Item quantities still total seven, so one correct-looking column can hide the error elsewhere.

PostgreSQL’s table-expression documentation describes a qualified join as producing a row for each matching pair. That logical relationship explains the repeated order values; this exercise executes SQLite, not PostgreSQL.

The missing customers expose another defect. Bo’s order is pending, so the WHERE condition rejects it. Cy’s unmatched order columns are NULL, so o.status = 'paid' does not evaluate to true and the row disappears too.

SQLite’s SELECT reference explains this distinction: outer joins add unmatched rows after applying ON, while WHERE can remove them. This describes the logical result, not a promised physical execution order or query plan.

Aggregate item quantities before joining

Build a summary containing at most one row per order, then join it to qualified orders. A common table expression, named item_totals here, lets you express that intermediate result:

WITH item_totals AS (
  SELECT order_id, SUM(quantity) AS item_units
  FROM order_items
  GROUP BY order_id
)
SELECT c.customer_id, c.customer_name,
       COUNT(o.order_id) AS paid_orders,
       COALESCE(SUM(o.amount_cents), 0) AS paid_amount_cents,
       COALESCE(SUM(t.item_units), 0) AS item_units
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id AND o.status = 'paid'
LEFT JOIN item_totals AS t ON t.order_id = o.order_id
GROUP BY c.customer_id, c.customer_name
ORDER BY c.customer_id;

The summary has three units for order 101 and four for order 102. Joining it no longer repeats either order. Ada’s two amounts sum to 2000, and the item totals sum to seven.

The paid condition now determines which orders match each customer. Bo has no qualifying order, and Cy has no order at all, but the outer join retains both customers. Dee’s order 401 qualifies even though the item summary has no row for it.

Run the query and compare every row with the required output above, including the zeros and ordering. The result should contain exactly four rows. An answer containing only Ada could demonstrate a correct total for Ada while still violating the contract.

Explain the count, empty sums and DISTINCT trap

COUNT(o.order_id) counts matched orders because those supplied IDs are non-NULL. For Bo and Cy, the outer join supplies a row with a NULL order ID. COUNT(*) would count that row as one, even though it does not represent a qualifying order.

SQLite’s aggregate reference distinguishes row counts from counts of non-NULL values. It also explains that SUM ignores NULL values and returns NULL when it has no non-NULL input. The explicit COALESCE(..., 0) supplies zero where this exercise requires it.

PostgreSQL’s aggregate documentation corroborates those count and empty-sum distinctions. Neither source establishes that unknown real-world amounts should always become zero. Here, the fixture has no recorded NULL amounts or quantities; the missing values come from unmatched joins.

Adding COUNT(DISTINCT o.order_id) to the wrong query can fix Ada’s order count, but her amount remains inflated. Replacing the sum with SUM(DISTINCT o.amount_cents) creates a different error: both legitimate orders have amount 1000, so their distinct amounts sum to only 1000.

DISTINCT removes duplicate argument values before aggregation. It does not know that two equal amounts belong to different orders. Preserve the order identity and row structure rather than trying to deduplicate money values afterward.

Check four independent changes

Apply each change independently to the original fixture, not cumulatively. Predict the new result before running the corrected query.

Change to the original data Expected effect
Add item 1005 to order 102 with quantity 1 Ada remains at 2 orders and 2000 cents; item units become 8.
Change order 102 to pending Ada becomes 1 order, 1000 cents and 3 units.
Add paid order 202 for Bo, amount 250, with no items Bo becomes 1 order, 250 cents and 0 units.
Remove both item rows belonging to order 101 Ada remains at 2 orders and 2000 cents; item units become 4.

The other customers’ outputs remain unchanged in each case. These checks separate order qualification, order identity and item quantity. They can expose mistakes in this small contract; they do not establish production performance or cover every possible data model.

Explain the solution aloud: “I need one row per customer. I first sum quantities per order, preserving at most one summary row per order. I then join paid orders without removing unmatched customers and calculate their totals.” Follow that with the failing order ID and the zero-match cases.

For a different exercise, try the worked Python coding problem or choose an unfamiliar prompt from the question bank. Rebuild the reasoning rather than memorizing this query.

For spoken rehearsal, Prepin offers role-focused voice mock interviews with feedback. Practice this interview.

Frequently asked questions

What if two customers have the same name?

Keep their customer IDs separate. The query groups by ID and name, so equal display names do not merge two customers. Grouping only by name would change the output contract.

Can I run this exact query in another SQL database?

The walkthrough uses SQLite. Check the target database’s syntax and type behavior, then run the complete fixture there before claiming equivalent results. The PostgreSQL documentation supports the explanations; it is not evidence that this code was executed on PostgreSQL.

Should a paid order without items disappear?

No, under this contract it contributes one order, its amount and zero item units. An inner join to the item summary would remove it. Change that behavior only if the requested result changes.

Do these checks prove that the query is ready for production?

No. They check a small, stated set of inputs and changes. Production work needs its own data rules, types, workload evidence and verification; a common table expression is not itself a speed guarantee.

Sources

Prepared with AI assistance. Official documentation checked November 22, 2026. The fixture, question and expected results are original fictional teaching material, not measured hiring or business results.

Oleh Savchuk, founder of Prepin

Oleh Savchuk

Founder, Prepin

Oleh Savchuk is the founder of Prepin, an AI hiring marketplace where recruiters screen engineers with AI-run technical interviews and candidates practice for theirs. He is a software engineer in Philadelphia and writes about technical hiring, AI in recruiting and interview preparation.

Practice the interview out loud

Run a realistic AI mock interview for your target role and get feedback on every answer.

Start a mock interview →

Screen engineers before the first call

Send candidates one link. Prepin runs the technical voice interview and live coding, then gives you a scored report.

See how screening works →