Customer Spend Percentile
Whetcode Supply, an online desk-gear shop, is adding a standing to every customer's account page. Marketing refuses to print raw dollar totals — a customer who has spent sixty dollars should not be told they are small, and a total means nothing to a reader who doesn't know what everyone else spent. What they want instead is a number between 0 and 1 saying where each customer sits relative to the rest of the customer base.
The page is being rolled out by signup cohort, newest accounts first, so each release covers the accounts created on or after one date — and that date comes with the request. The standing itself does not move as the rollout widens: it is measured against every customer who has spent something, not just the ones the page has reached.
Three things make this fiddly. An order records what was bought and how many, never what it cost — money lives only in the catalogue. A customer's standing cannot be worked out from their own rows alone; it depends on everybody else's totals too. And one account has never ordered at all, so there is no total to place it against the others.
customers_df — one row per customer.
| id | name | city | signup_date |
|---|---|---|---|
| 1 | Priya Nair | Austin | 2022-01-15 |
| 2 | Tom Becker | Berlin | 2022-03-02 |
| 3 | Sofia Rossi | Milan | 2022-05-19 |
| 4 | Liam OConnor | Dublin | 2023-01-08 |
| 5 | Wei Zhang | Austin | 2023-04-27 |
| 6 | Ana Mendes | Austin | 2023-09-12 |
products_df — the catalogue. price is the cost of a single unit.
| id | name | category | price |
|---|---|---|---|
| 1 | Wireless Mouse | electronics | 25.0 |
| 2 | Standing Desk | furniture | 350.0 |
| 3 | Desk Lamp | furniture | 40.0 |
| 4 | Mechanical Keyboard | electronics | 85.0 |
| 5 | Notebook Set | office | 12.0 |
orders_df — one row per order placed this year.
| id | customer_id | product_id | quantity | order_date |
|---|---|---|---|---|
| 1 | 1 | 1 | 2 | 2023-02-01 |
| 2 | 1 | 4 | 1 | 2023-02-01 |
| 3 | 2 | 2 | 1 | 2023-02-10 |
| 4 | 3 | 3 | 3 | 2023-03-05 |
| 5 | 4 | 5 | 5 | 2023-03-11 |
| 6 | 1 | 3 | 1 | 2023-04-02 |
| 7 | 5 | 1 | 4 | 2023-05-20 |
| 8 | 2 | 5 | 2 | 2023-05-22 |
| 9 | 3 | 4 | 1 | 2023-06-01 |
| 10 | 5 | 2 | 1 | 2023-06-15 |
Input
All the tables above are already built for you — the customer, catalogue and order rows are not read from input. What does arrive is a single line holding the cohort's earliest signup date, written the way signup_date is written, as YYYY-MM-DD.
Task: Print a dict mapping the name of every customer who signed up on or after that date to their standing, with those customers in id order. A customer's total spend is what all of their orders came to at catalogue prices. Their standing is their position when every customer who has spent something is lined up from smallest total to largest, divided by how many of those there are — so the biggest spender scores 1.0 and, with five of them, the smallest scores 0.2. Customers with identical totals share the average of the positions they occupy. Round every standing to 2 decimals. A customer who has never ordered has no standing to report: if they fall in the cohort their entry still appears, but its value is the missing-value marker, which prints as nan. If no account signed up on or after the date, print an empty dict.
Example: the printed dict looks like {'Some Customer': 0.5, 'Another Customer': 1.0, 'A Third Customer': nan}.
Sign in to solve this problem
Reading problems is free for everyone — solving them (Run, Submit, and tracking what you've solved) needs an account.
Sign in