Several purchases by the same customer in the same product line on the same date count as one purchase. For each product line (Phone, Laptop, Tablet, Watch …) return `product_line`, `buyers` (distinct customers), `repeat_buyers` (customers with purchases on 2+ different dates in that line), and `avg_upgrade_days` = average number of days between a customer's consecutive purchase dates in that line, over all such gaps (ROUND 1; NULL if there are no gaps). Order by `product_line`.
| Column | Type |
|---|---|
| purchase_id | INTEGER |
| customer_id | INTEGER |
| product_line | TEXT |
| model | TEXT |
| purchase_date | DATE |
| purchase_id | customer_id | product_line | model | purchase_date |
|---|---|---|---|---|
| 1 | 1 | Phone | iPhone 14 | 2022-09-20 |
| 2 | 1 | Phone | iPhone 16 | 2024-09-25 |
| 3 | 2 | Phone | iPhone 15 | 2023-10-01 |
| 4 | 3 | Phone | iPhone 13 | 2021-09-30 |
| 5 | 3 | Phone | iPhone 15 | 2023-09-29 |
| 6 | 3 | Phone | iPhone 17 | 2025-10-02 |
| 7 | 1 | Laptop | MacBook Air | 2023-01-10 |
| 8 | 4 | Laptop | MacBook Pro | 2022-06-01 |
| product_line | buyers | repeat_buyers | avg_upgrade_days |
|---|---|---|---|
| Laptop | 2 | 1 | 1461 |
| Phone | 3 | 2 | 733 |
| Tablet | 1 | 0 | NULL |
Solve it in the live editor on this page — run on sample data, then submit against hidden tests.
‹ Creators Who Posted Every Campaign Week · App Store Year-over-Year Revenue by Category ›
{{ totalCount }} questions in the style asked at Amazon, Microsoft, Google, Meta, Flipkart and more — {{ hardPct }}% Hard. Write SQL in a real database engine in your browser, run it on sample data, then evaluate against hidden tests.
Company tags show where this type of question is commonly reported in interviews. Questions, data and solutions are written by TechTown.
{{ cur.statement }}
No submissions yet. Press Evaluate to submit your query.
{{ solutionSql }}
{{ out.error }}
You have solved your 3 free questions — nice work! Log in (free) to unlock all 150 questions, keep your streak and appear on the leaderboard.