Your Remote Career Friendby r2studio.dev
Your Remote Career Friend

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)

CUSTOMERREVENUEORDER
Budi MartRp18,2jt14
Sari GrosirRp15,7jt11
Toko JayaRp12,4jt9

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."

Spot check: manually sum 2–3 orders of Budi Mart with a calculator — must match. Traps + full answer: /resources/sql-join-contoh.

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.
Book a session