The brief
Toko Kita is a small Indonesian online shop that sells coffee, kitchen equipment, stationery, home goods and a few electronics. All of its orders live in a PostgreSQL database, and until now the only way to get numbers out of it was to ask the one person who knew where everything was. You've just joined, and three teams have each sent you a request.
Marketing wants a list of their best customers. The owner wants a monthly revenue report that shows how the year is building up. And the warehouse wants a short list every Monday of the products that need attention. Each one is a single query, but each one needs several things from the SQL track working together: joins, grouping, CTEs, window functions and CASE.
How the tasks work
Each task is checked automatically, like the module questions: your query runs against the shop's database and the result has to match, column names and row order included. The tickets tell you what the result must look like, not how to write the query. There are hints if you get stuck, but try each one without opening the solution first.
The tasks
| # | Task | Main skills |
|---|---|---|
| 1 | Best customers | Joins, GROUP BY, CTEs, ROUND and casts |
| 2 | Monthly revenue with a running total | Grouping by month, window functions (SUM() OVER, LAG) |
| 3 | The Monday restock report | LEFT JOIN, COALESCE, CASE, filtering on a computed column |
The database
These are the four tables the tasks use:
| Table | Columns |
|---|---|
customers | customer_id, name, email, city, signup_date, segment |
products | product_id, name, category, price, cost, stock |
orders | order_id, customer_id, order_date, status, channel |
order_items | order_id, product_id, quantity, unit_price |
An order has one row in orders and one row in order_items for each product in it. The money for an item is quantity * unit_price, using the price at the time of the order (which isn't always today's products.price). An order's status is completed, shipped, pending or cancelled, and only completed orders count as sales in these reports. One more thing to watch: products.stock is NULL for a product nobody has counted yet.
When you're ready, open Task 1.