Customers With the 8th Highest Lifetime Purchase
Problem statement
An e-commerce analytics team wants to know which customers sit at a specific rank of lifetime spend. Write the query that finds the customers with the 8th highest lifetime purchase, then show how you would get the same answer in Python.
Tables (SQLite dialect):
customers(cust_id INT, name TEXT): one row per customer;cust_idis unique, but two customers can share a name.transactions(cust_id INT, transaction_amt INT): one row per purchase, many rows per customer; amounts are positive integers.
A customer's lifetime purchase is the sum of their transaction_amt. Only customers that appear in customers and have at least one transaction are ranked; transactions whose cust_id is not in customers are ignored. Rank the distinct lifetime totals from highest to lowest, so customers with equal totals share a rank and the next total gets the next rank. Return the name of every customer whose total equals the 8th highest distinct total, one row per customer, as a single column name ordered by name ascending. If there are fewer than 8 distinct totals, return no rows.
Example
customers:
cust_id | name
1 | Asha
2 | Ben
3 | Chen
4 | Dara
5 | Eli
6 | Fatima
7 | Gopal
8 | Hana
9 | Ivan
10 | Jaya
11 | Kofi
transactions:
cust_id | transaction_amt
1 | 500
1 | 400
2 | 850
3 | 700
4 | 300
4 | 400
5 | 650
6 | 600
7 | 500
8 | 450
9 | 250
9 | 150
10 | 400
99 | 2000
Output:
name
Ivan
Jaya
Lifetime totals are Asha 900, Ben 850, Chen 700, Dara 700, Eli 650, Fatima 600, Gopal 500, Hana 450, Ivan 400 and Jaya 400. The distinct totals in order are 900, 850, 700, 650, 600, 500, 450, 400, so the 8th is 400. Kofi has no purchases, and customer 99 is not in customers.
Follow-up (not graded): write the same logic in Python with pandas, given the two tables as DataFrames.
Show hint
At what grain do you need the data before any ranking makes sense?
Relevant roles: Data Scientist and Forward Deployed Engineer.
Related free problems
- Pandas Aggregation and FilteringData WranglingEasy