Beginner Problems

Focus: selection, filtering, grouping, joins, NULLs, and basic windows.

1. Return all paid orders from 2026-06-01.

Use a half-open timestamp range.

SELECT order_id, customer_id, order_ts, total_amount
FROM orders
WHERE status = 'paid'
	AND order_ts >= TIMESTAMP '2026-06-01 00:00:00'
	AND order_ts < TIMESTAMP '2026-06-02 00:00:00';

2. Count customers by country, including NULL country as unknown.

SELECT
	COALESCE(country, 'unknown') AS country,
	COUNT(*) AS customers
FROM customers
GROUP BY COALESCE(country, 'unknown');

3. Compute total paid revenue per customer.

SELECT
	customer_id,
	SUM(total_amount) AS paid_revenue
FROM orders
WHERE status = 'paid'
GROUP BY customer_id;

4. Return customers with at least three paid orders.

SELECT customer_id
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING COUNT(*) >= 3;

5. Return every customer and their paid order count, including zero.

SELECT
	c.customer_id,
	COUNT(o.order_id) AS paid_order_count
FROM customers c
LEFT JOIN orders o
	ON o.customer_id = c.customer_id
	AND o.status = 'paid'
GROUP BY c.customer_id;

6. Find customers who have never placed an order.

SELECT c.customer_id
FROM customers c
WHERE NOT EXISTS (
	SELECT 1
	FROM orders o
	WHERE o.customer_id = c.customer_id
);

7. Find the latest order per customer.

WITH ranked AS (
	SELECT
		o.*,
		ROW_NUMBER() OVER (
			PARTITION BY customer_id
			ORDER BY order_ts DESC, order_id DESC
		) AS rn
	FROM orders o
)
SELECT customer_id, order_id, order_ts, total_amount
FROM ranked
WHERE rn = 1;

8. Count distinct active users per day.

Assume event_date is derived from event_ts.

SELECT
	event_date,
	COUNT(DISTINCT user_id) AS active_users
FROM events
GROUP BY event_date;

9. Return products that have never been ordered.

SELECT p.product_id
FROM products p
WHERE NOT EXISTS (
	SELECT 1
	FROM order_items oi
	WHERE oi.product_id = p.product_id
);

10. Compute average order value for paid orders.

SELECT AVG(total_amount) AS avg_paid_order_value
FROM orders
WHERE status = 'paid';

If total_amount can be NULL, AVG ignores NULLs. Decide whether that is correct.

11. Find customers whose country is not US, including unknown country.

SELECT customer_id
FROM customers
WHERE country <> 'US' OR country IS NULL;

country <> 'US' alone excludes NULLs.

12. Compute paid and cancelled order counts in one query.

SELECT
	SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_orders,
	SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_orders
FROM orders;

13. Return daily paid revenue with zeroes for days with no orders.

SELECT
	d.calendar_date,
	COALESCE(SUM(o.total_amount), 0) AS paid_revenue
FROM date_spine d
LEFT JOIN orders o
	ON o.order_date = d.calendar_date
	AND o.status = 'paid'
GROUP BY d.calendar_date;

The date spine preserves missing days.

14. Find duplicate customer rows by email.

SELECT email, COUNT(*) AS rows
FROM customers
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1;

15. Join orders to customers and keep only paid orders from paid-search customers.

SELECT
	o.order_id,
	o.customer_id,
	o.total_amount,
	c.acquisition_channel
FROM orders o
JOIN customers c
	ON c.customer_id = o.customer_id
WHERE o.status = 'paid'
	AND c.acquisition_channel = 'paid_search';

Check that customers.customer_id is unique before treating this as a safe enrichment join.

16. Get each customer's first signup-to-order lag.

Interval/difference syntax varies by engine. First get the timestamps.

WITH first_order AS (
	SELECT customer_id, MIN(order_ts) AS first_order_ts
	FROM orders
	WHERE status = 'paid'
	GROUP BY customer_id
)
SELECT
	c.customer_id,
	c.signup_ts,
	f.first_order_ts
FROM customers c
LEFT JOIN first_order f
	ON f.customer_id = c.customer_id;

Then compute the time difference with your database’s date/time function.

17. Find orders whose item totals do not match order total.

WITH item_totals AS (
	SELECT
		order_id,
		SUM(quantity * unit_price) AS item_total
	FROM order_items
	GROUP BY order_id
)
SELECT
	o.order_id,
	o.total_amount,
	i.item_total
FROM orders o
JOIN item_totals i
	ON i.order_id = o.order_id
WHERE o.total_amount <> i.item_total;

For money, real systems need rounding/currency tolerance.

18. Return the top 10 customers by paid revenue.

SELECT
	customer_id,
	SUM(total_amount) AS paid_revenue
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
ORDER BY paid_revenue DESC
FETCH FIRST 10 ROWS ONLY;

Some engines use LIMIT 10.