Real Business SQL Scenarios
Expert⏱️ 30 mins read
What You'll Learn
Expert SQL in industry means writing queries for business problems across payments, banking, fraud detection, e-commerce, and marketing. These require combining multiple concepts: window functions, CTEs, CASE WHEN, and date math in single complex queries.
Syntax
-- No single syntax — combines all advanced patterns
-- CTEs + Window Functions + Aggregates + CASE WHENExample
-- Payments: daily GMV and failure rate
-- (standalone scenario — real domains often bring their own schema)
SELECT
DATE(txn_time) AS day,
COUNT(*) AS total_txns,
SUM(amount) AS gmv,
ROUND(SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END)
* 100.0 / COUNT(*), 2) AS failure_rate_pct
FROM transactions
GROUP BY DATE(txn_time);
-- E-commerce: Customer Lifetime Value — runs on ShopCo's orders
SELECT
customer_id,
COUNT(*) AS total_orders,
SUM(total) AS lifetime_value,
ROUND(AVG(total), 2) AS avg_order_value,
DATEDIFF(MAX(order_date), MIN(order_date)) AS days_active
FROM orders
GROUP BY customer_id
ORDER BY lifetime_value DESC;
-- ShopCo: monthly revenue split by outcome (Topics 9 + 11 combined)
SELECT
DATE_FORMAT(order_date, '%Y-%m') AS month,
SUM(CASE WHEN status = 'shipped' THEN total ELSE 0 END) AS shipped_rev,
SUM(CASE WHEN status != 'shipped' THEN total ELSE 0 END) AS open_rev
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m');Beyond the Basics
Data in play — reference tables for this topic
-- orders (from Topic 7) — the CLV and monthly queries run on this
-- id | customer_id | status | total | order_date
-- 1 | 1 | shipped | 240.00 | 2024-01-15
-- 2 | 2 | shipped | 89.90 | 2024-02-03
-- 3 | 1 | pending | 430.00 | 2024-03-11
-- 4 | 3 | shipped | 59.50 | 2024-04-02
-- 5 | 4 | cancelled | 120.00 | 2024-05-20
--
-- payments example uses a standalone transactions table
-- (txn_time, amount, status) — NOT part of ShopCoCLV on ShopCo — and the customer it drops
The CLV query is Topic 8's aggregates plus Topic 14's DATEDIFF, grouped per customer. Run it and customer 5 (Maike) is missing entirely — she has no orders, and GROUP BY over orders can only see customers who ordered. That is not a bug; it is a definition. If lifetime value should include zero-value customers, the query needs Topic 15's LEFT JOIN.
-- From the example, traced:
-- 1 | 2 | 670.00 | 335.00 | 56 (Karl — Jan 15 to Mar 11)
-- 2 | 1 | 89.90 | 89.90 | 0 (Ines)
-- 3 | 1 | 59.50 | 59.50 | 0 (Omar)
-- 4 | 1 | 120.00 | 120.00 | 0 (Sofia)
-- Maike: absent. Add her:
-- FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
-- → Maike | 0 | 0.00 | NULL | NULL — COALESCE to taste (Topic 7)Failure rates: Topic 11's CASE-aggregate pattern, productionized
GMV and failure rate in one pass is the same conditional-aggregation skeleton from Topic 8's deep dive and Topic 11's pivot — multiplied by a payment volume where the ratio matters. The pattern scales unchanged; only the stakes change. Note the 100.0 (not 100): integer division would report 0 or 100 and nothing between.
ROUND(SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END)
* 100.0 / COUNT(*), 2) AS failure_rate_pct
-- 3 failures in 1000 txns → 3 * 100.0 / 1000 = 0.30 ✓
-- with * 100 instead: 3 * 100 / 1000 → integer math → 0 ✗Fraud detection: time-window grouping
'Five transactions in one hour' is Topic 10's HAVING with a sliding WHERE window: filter to the last hour, group by account, flag the heavy groups. It catches bursts but not drift — and the moment 'sliding' matters more than 'bucketed', Topic 18's window frames take over (RANGE BETWEEN INTERVAL... in modern engines).
-- Bucketed (simple, from the pattern):
SELECT account_id, COUNT(*) AS txn_count
FROM transactions
WHERE txn_time >= NOW() - INTERVAL 1 HOUR
GROUP BY account_id
HAVING COUNT(*) >= 5;
-- Sliding, window-style (PostgreSQL):
SELECT * FROM (
SELECT account_id, txn_time,
COUNT(*) OVER (PARTITION BY account_id
ORDER BY txn_time RANGE BETWEEN INTERVAL '1 hour' PRECEDING
AND CURRENT ROW) AS window_count
FROM transactions
) t WHERE window_count >= 5;Common Mistakes
Not handling timezone offsets in payment systems (always store in UTC, convert at query time). Double-counting revenue when joining tables with one-to-many relationships. Not filtering test/internal accounts from analytics.
Interview Tips
For analytics roles, you'll be given a business question and a schema — practice translating natural language to SQL. Always clarify: time range, timezone, how to handle NULLs, and whether to include test data. Think out loud during the interview.
Official References
Test Yourself — 6 questions
1. The ShopCo CLV query (GROUP BY customer_id over orders) silently drops which customer?
2. Karl's CLV row reads…?
3. Why write failure_rate * 100.0 instead of * 100?
4. Joining orders to order_items and SUMming order.total causes…?
5. 'Five transactions in one hour' per account is best caught by…?
6. The payments GMV example uses a transactions table that is not part of ShopCo. Why keep it?
Practice
Return the names of the top 3 customers by total spend across all their orders.
⚡ Solve it in the SQL playground →Frequently Asked Questions
How do I avoid double-counting revenue when joining one-to-many tables?
Before joining, decide the grain. Joining orders to order_items multiplies order rows by their items — SUM(order.total) then counts each order once per item. Either aggregate items separately in a CTE and join the results, or use SUM(DISTINCT ...) with extreme care (it deduplicates values, not rows — two identical 50.00 orders collapse to one).
Should analytics queries filter out test data?
Yes — and the filter belongs in a reusable place (a view or CTE), not scattered as WHERE email NOT LIKE '%test%' across queries. Test accounts, internal orders, and refunds each distort different metrics; decide per metric and centralize the decision.