Mock interview. You're interviewing for a senior analyst job on an app store's developer relations team. The interviewer says: "Developers make money three ways: paid downloads, in-app purchases and subscriptions. We want one clean view of the money, then a few cuts of it."
The rules for this interview:
is_redownload false) of an app with price > 0, worth the app's price.purchases is a sale worth price_usd. If refunded_on has a date, the money went back to the customer.small_business true), 30% for everyone else. The rest is the developer's proceeds."Put paid downloads and purchases into one list of sales, then show for each sale type: number of sales, gross amount, refunds and net (gross minus refunds)."
Expected output
sale_type is paid download for downloads and the purchase's product_type otherwise; refunds is 0 when there are none; sorted by net, highest firstExample of the shape (made-up values):
| sale_type | sales | gross | refunds | net |
|---|---|---|---|---|
| paid download | 2000 | 8000.00 | 0 | 8000.00 |
Hints, the later steps and the answer come with Pro.
Step 1 of 4
Write your query here. Ctrl + Enter submits.