Each user's top category
hardEach user's top category
Pinterest SQL Interview Question
Pinterest personalizes each user's home feed around the category their pins get saved most in.
For each user, find the category whose pins have the most saves in total. Return user_id, category and total_saves. If two categories tie, pick the one that comes first alphabetically. Sort by user_id.
Asked of
- Data Analyst
- Business Analyst
- BI Analyst
- Product Analyst
- Analytics Engineer
pinsTable20 rows
| Column Name | Type |
|---|---|
| pin_id | BIGINT |
| user_id | BIGINT |
| category | VARCHAR |
| created_at | DATE |
| saves | BIGINT |
pinsExample Input
| pin_id | user_id | category | created_at | saves |
|---|---|---|---|---|
| 1 | 701 | Food | 2024-07-01 | 14 |
| 2 | 701 | Food | 2024-07-01 | 3 |
| 3 | 702 | Travel | 2024-07-02 | 27 |
| 4 | 701 | DIY | 2024-07-02 | 8 |
| 5 | 703 | DIY | 2024-07-03 | 5 |
| 6 | 702 | Travel | 2024-07-03 | 11 |
| 7 | 704 | Food | 2024-07-04 | 2 |
| 8 | 703 | Travel | 2024-07-04 | 19 |
| 9 | 702 | Food | 2024-07-05 | 6 |
| 10 | 704 | Food | 2024-07-05 | 9 |
| 11 | 705 | DIY | 2024-07-06 | 31 |
| 12 | 703 | DIY | 2024-07-06 | 4 |
| 13 | 701 | Travel | 2024-07-07 | 12 |
| 14 | 705 | DIY | 2024-07-07 | 7 |
| 15 | 704 | DIY | 2024-07-08 | 1 |
| 16 | 702 | Travel | 2024-07-08 | 22 |
| 17 | 705 | Food | 2024-07-09 | 10 |
| 18 | 703 | Food | 2024-07-09 | 3 |
| 19 | 704 | Travel | 2024-07-10 | 16 |
| 20 | 701 | Food | 2024-07-10 | 5 |
Example Output
| user_id | category | total_saves |
|---|---|---|
| 701 | Food | 22 |
| 702 | Travel | 60 |
| 703 | Travel | 19 |
| 704 | Travel | 16 |
| 705 | DIY | 38 |
Explanation
User 701's Food pins were saved 14, 3 and 5 times, 22 in total, more than their Travel pin with 12 saves or their DIY pin with 8. User 703's single Travel pin has 19 saves, more than their two DIY pins combined, which have 9.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Pinterest
Difficulty
hard
Topic
window functions
Language
SQL
Your query
Starting the SQL engine...