TASK · intermediate · 2 hours
Answer 3 business questions with 1 JOIN query
Join order and customer tables to answer revenue questions
01
Why it matters
JOIN plus GROUP BY is 80 percent of daily analyst work and what interviews test.
02
Steps
- Get a sample dataset (orders plus customers, or make 50 rows)
- JOIN both tables on customer_id
- Compute revenue per customer last month (GROUP BY plus SUM)
- Show top 10 plus average order
- Write 3 lines of insight in human language
03
Expected output
Query .sql plus result table plus 3 insights
04
Checklist
- JOIN key correct (check duplicates)
- Numbers pass sanity checks
- Insights cite specific numbers
05
Evidence
Commented SQL file plus result screenshot
06
Self review
Would your query still be correct at 10x the data?
WORKED EXAMPLE · TOP-10 QUERY
The finished file, end to end
Your submission should look like this: commented query, result table, 3-line insight.
A · QUERY (.sql)
-- grain: 1 baris = 1 customer, revenue bulan lalu
-- cek key dulu: SELECT customer_id, COUNT(*) FROM orders GROUP BY 1 HAVING COUNT(*)>1 (di sisi orders wajar >1; di sisi customers harus unik)
SELECT c.nama, SUM(o.nominal) AS revenue, COUNT(*) AS n_order
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.tanggal >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
GROUP BY c.nama ORDER BY revenue DESC LIMIT 10;B · HASIL (3 BARIS TERATAS)
| CUSTOMER | REVENUE | ORDER |
|---|---|---|
| Budi Mart | Rp18,2jt | 14 |
| Sari Grosir | Rp15,7jt | 11 |
| Toko Jaya | Rp12,4jt | 9 |
C · INSIGHT 3 BARIS
"Top 3 customers contribute 41% of revenue (Rp54jt of Rp132jt). Their average order is Rp1,3jt vs Rp480rb overall. Recommendation: offer the subscription bundle to the top 3 before spending on new ads."
NEXT TASKS
TOOLS USED
TURN IT INTO PROOF
STUCK OR UNSURE?
bring your learning to a 1-on-1All materials are free to study. If positioning, portfolio, or apply strategy stalls, we dissect it together.