Repeated Category Purchase
0:00:00
You’re analyzing a user’s purchases for a retail business.
Each product belongs to a category. Your task is to identify which purchases represent the first time the user has bought a product from its own category and which purchases represent repeat purchases within the product category.
The id in the purchases table represents the purchase order (rows with a lower id are earlier purchases).
Your code should output a table that includes every purchase the user has made. Additionally, the table should include a boolean column with a value of 1 if the user has previously purchased a product from its category and 0 if it’s their first time buying a product from that category.
Note: Sort the results by the time purchased, in ascending order.
Schema
Input:
purchases table
| Column | Type |
|---|---|
id |
INTEGER |
product_name |
VARCHAR |
product_category |
INTEGER |
Output:
| Column | Type |
|---|---|
product_name |
VARCHAR |
category_previously_purchased |
BOOLEAN |
Display results in the following way:
| product_name | category_previously_purchased |
|---|---|
| toy car | 0 |
| toy plane | 1 |
| basketball | 0 |
| football | 1 |
| baseball | 1 |
Example
Input:
purchases table
| id | product_name | product_category |
|---|---|---|
| 1 | toy car | 1 |
| 2 | basketball | 2 |
| 3 | toy car | 1 |
| 4 | basketball | 2 |
| 5 | baseball | 2 |
| 6 | robot X1 | 3 |
| 7 | robot X2 | 3 |
| 8 | toy plane | 1 |
| 9 | robot X3 | 3 |
| 10 | teddy bear | 5 |
Output:
| product_name | category_previously_purchased |
|---|---|
| toy car | 0 |
| basketball | 0 |
| toy car | 1 |
| basketball | 1 |
| baseball | 1 |
| robot X1 | 0 |
| robot X2 | 1 |
| toy plane | 1 |
| robot X3 | 1 |
| teddy bear | 0 |
.
.
.
.
Comments