Practice Sequence and Hierarchy
Explore SQL sequence and hierarchy patterns through practical exercises that involve ranking top products by category, analyzing restocking intervals, tracing customer referral chains recursively, and identifying consecutive-day order streaks. This lesson enhances your ability to write complex queries and apply structured approaches commonly tested in technical interviews.
Sequence and hierarchy patterns questions
Let’s practice writing queries to strengthen your grasp of sequence and hierarchy patterns.
Top product per category with ties allowed
Given the following structure of the Products table:
Field | Type |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Where ProductID is the primary key and CategoryID is the foreign key from the Categories table. The table contains information about products.
Write an SQL query to return the top-selling product(s) in each category. Return ProductName, CategoryID, MonthlySales, and sales_rank.
Expected output:
| ProductName | CategoryID | MonthlySales | sales_rank |
|---|---|---|---|
| Wireless Earbuds | 1 |