Diagnose the E-commerce Revenue Drop
Revenue fell 17% in September at a home-goods retailer. The CEO thinks the market softened. The data says otherwise — find what actually happened and tell them in one page.
The brief
You have just joined Northwind Living, a direct-to-consumer home goods retailer, as their first analyst.
On your second day the CEO forwards you a board email:
"September revenue is down badly on August. The market has clearly turned — competitors are discounting hard and customers are pulling back. I want to cut the paid social budget by 40% next quarter. Can someone confirm the numbers before Thursday?"
Nobody has checked whether that story is true. You have thirteen months of order-level data and until Thursday.
Your job is not to confirm the CEO's theory. It is to find out what actually happened, decide whether cutting paid social would help or hurt, and say so in language a board will act on.
Three things worth knowing before you start, because they have burned people already:
The finance team reports in IST. The database stores timestamps in UTC.
A payment gateway change went live in late August. Engineering mentioned something about retry behaviour but nobody followed up.
Refunds are recorded as separate rows, not adjustments to the original order.
Treat everything else as you would on a real first week: assume nothing, check the shape of the data before you trust a number, and be ready to defend every figure you put in front of the CEO.
The data
Three files, ~2 MB total.
orders.csv — one row per order line. Columns: order_id, customer_id, order_ts_utc, sku, category, quantity, unit_price, discount_code, discount_amount, revenue, channel, device, is_refund.
customers.csv — customer_id, signup_date, region.
products.csv — sku, product_name, category, list_price.
The data covers December 2025 through October 2026. It is synthetic, generated for this challenge, and contains the kind of mess real order tables contain. No customer in it is a real person.
What to submit
- 1A public GitHub repository containing your SQL (or Python/R) and a README with your findings
- 2Your answers to the five objective questions, entered on the submission form
- 3A chart or dashboard link showing the trend you found — Tableau Public, Looker Studio, Power BI publish-to-web, or an image in the repo
- 4A recommendation of 200 words or less in the README: what you would tell the CEO about the paid social cut
- 5Optional: a 3-minute Loom walking through your reasoning
Rules
- Scoring is 60% the objective answers, which are graded automatically, and 40% your analysis and recommendation, scored against a published rubric
- Scoring is automated. The top 10 submissions and any disputed score are reviewed by a human before prizes
- Your repository must be public and its commit history must show the work happening before the deadline
- Work individually. You may use any tool, including AI assistants — but you must be able to explain every number you submit
- One submission per person. You can update it any time before the deadline
- Do not publish your solution publicly until judging closes, so others can still attempt it
Not open yet
The dataset and brief are published above so you can prepare.
See all challenges →