runtimetask RANK / DENSE_RANK · medium
Bestsellers within each category
For sale lines in 2011, return category_id, stock_code and units for each category’s top three products by units sold, including ties (use RANK).
what to return
- Columns
category_idstock_codeunitsin this order- Rows
- 49 rows · any order
- Skill
- Rank within groups and keep the top N including ties. Refresher: Top N per group & ties →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result49 rows · any order
| category_id | stock_code | units |
|---|---|---|
| 1 | 22952 | 1708 |
| 1 | 22086 | 1275 |
| 1 | 22910 | 1054 |
first 3 of 49 rows
data you can query
Retail dataset: the first 6 rows of each table. Under each column name: the kind of value it holds, and which table it links to. Your queries run on all rows.
orders6 columns · 6,722 rows
| order_idnumber · unique id | invoice_notext | customer_idnumber · links to customers | ordered_atdate & time | countrytext | order_typetext |
|---|---|---|---|---|---|
| 1 | 489438 | 18102 | 2009-12-01 09:24:00 | United Kingdom | sale |
| 2 | 489517 | 16329 | 2009-12-01 11:34:00 | United Kingdom | sale |
| 3 | C489528 | 16714 | 2009-12-01 11:50:00 | United Kingdom | cancellation |
| 4 | 489529 | 17984 | 2009-12-01 11:51:00 | United Kingdom | sale |
| 5 | 489532 | 13394 | 2009-12-01 11:58:00 | United Kingdom | sale |
| 6 | 489540 | 12913 | 2009-12-01 12:18:00 | United Kingdom | sale |
- order_idnumber · unique id
- 1
- invoice_notext
- 489438
- customer_idnumber · links to customers
- 18102
- ordered_atdate & time
- 2009-12-01 09:24:00
- countrytext
- United Kingdom
- order_typetext
- sale
- order_idnumber · unique id
- 2
- invoice_notext
- 489517
- customer_idnumber · links to customers
- 16329
- ordered_atdate & time
- 2009-12-01 11:34:00
- countrytext
- United Kingdom
- order_typetext
- sale
- order_idnumber · unique id
- 3
- invoice_notext
- C489528
- customer_idnumber · links to customers
- 16714
- ordered_atdate & time
- 2009-12-01 11:50:00
- countrytext
- United Kingdom
- order_typetext
- cancellation
- order_idnumber · unique id
- 4
- invoice_notext
- 489529
- customer_idnumber · links to customers
- 17984
- ordered_atdate & time
- 2009-12-01 11:51:00
- countrytext
- United Kingdom
- order_typetext
- sale
- order_idnumber · unique id
- 5
- invoice_notext
- 489532
- customer_idnumber · links to customers
- 13394
- ordered_atdate & time
- 2009-12-01 11:58:00
- countrytext
- United Kingdom
- order_typetext
- sale
- order_idnumber · unique id
- 6
- invoice_notext
- 489540
- customer_idnumber · links to customers
- 12913
- ordered_atdate & time
- 2009-12-01 12:18:00
- countrytext
- United Kingdom
- order_typetext
- sale
first 6 of 6,722 rows · your queries use all of them
order_items7 columns · 125,613 rows
| order_item_idnumber · unique id | order_idnumber · links to orders | line_numbernumber | stock_codetext · links to products | descriptiontext | quantitynumber | unit_pricedecimal |
|---|---|---|---|---|---|---|
| 1 | 1 | 1 | 21329 | DINOSAURS WRITING SET | 28 | 0.98 |
| 2 | 1 | 2 | 21252 | SET OF MEADOW FLOWER STICKERS | 30 | 1.69 |
| 3 | 1 | 3 | 21100 | CHARLIE AND LOLA CHARLOTTE BAG | 30 | 1.15 |
| 4 | 1 | 4 | 21033 | JUMBO BAG CHARLIE AND LOLA TOYS | 30 | 2 |
| 5 | 1 | 5 | 20711 | JUMBO BAG TOYS | 60 | 1.3 |
| 6 | 1 | 6 | 21410 | COUNTRY COTTAGE DOORSTOP GREEN | 32 | 2.5 |
- order_item_idnumber · unique id
- 1
- order_idnumber · links to orders
- 1
- line_numbernumber
- 1
- stock_codetext · links to products
- 21329
- descriptiontext
- DINOSAURS WRITING SET
- quantitynumber
- 28
- unit_pricedecimal
- 0.98
- order_item_idnumber · unique id
- 2
- order_idnumber · links to orders
- 1
- line_numbernumber
- 2
- stock_codetext · links to products
- 21252
- descriptiontext
- SET OF MEADOW FLOWER STICKERS
- quantitynumber
- 30
- unit_pricedecimal
- 1.69
- order_item_idnumber · unique id
- 3
- order_idnumber · links to orders
- 1
- line_numbernumber
- 3
- stock_codetext · links to products
- 21100
- descriptiontext
- CHARLIE AND LOLA CHARLOTTE BAG
- quantitynumber
- 30
- unit_pricedecimal
- 1.15
- order_item_idnumber · unique id
- 4
- order_idnumber · links to orders
- 1
- line_numbernumber
- 4
- stock_codetext · links to products
- 21033
- descriptiontext
- JUMBO BAG CHARLIE AND LOLA TOYS
- quantitynumber
- 30
- unit_pricedecimal
- 2
- order_item_idnumber · unique id
- 5
- order_idnumber · links to orders
- 1
- line_numbernumber
- 5
- stock_codetext · links to products
- 20711
- descriptiontext
- JUMBO BAG TOYS
- quantitynumber
- 60
- unit_pricedecimal
- 1.3
- order_item_idnumber · unique id
- 6
- order_idnumber · links to orders
- 1
- line_numbernumber
- 6
- stock_codetext · links to products
- 21410
- descriptiontext
- COUNTRY COTTAGE DOORSTOP GREEN
- quantitynumber
- 32
- unit_pricedecimal
- 2.5
first 6 of 125,613 rows · your queries use all of them
products6 columns · 5,304 rows
| stock_codetext · unique id | descriptiontext | category_idnumber · links to categories | list_pricedecimal | first_sold_atdate & time | is_merchandisenumber |
|---|---|---|---|---|---|
| 10002 | INFLATABLE POLITICAL GLOBE | 17 | 0.85 | 2009-12-01 09:08:00 | 1 |
| 10002R | ROBOT PENCIL SHARPNER | 8 | 5.17 | 2009-12-02 14:43:00 | 1 |
| 10080 | GROOVY CACTUS INFLATABLE | 17 | 0.39 | 2009-12-02 16:02:00 | 1 |
| 10109 | BENDY COLOUR PENCILS | 8 | 0.42 | 2009-12-03 12:31:00 | 1 |
| 10120 | DOGGY RUBBER | 17 | 0.21 | 2009-12-01 16:17:00 | 1 |
| 10123C | HEARTS WRAPPING TAPE | 8 | 0.65 | 2009-12-01 14:28:00 | 1 |
- stock_codetext · unique id
- 10002
- descriptiontext
- INFLATABLE POLITICAL GLOBE
- category_idnumber · links to categories
- 17
- list_pricedecimal
- 0.85
- first_sold_atdate & time
- 2009-12-01 09:08:00
- is_merchandisenumber
- 1
- stock_codetext · unique id
- 10002R
- descriptiontext
- ROBOT PENCIL SHARPNER
- category_idnumber · links to categories
- 8
- list_pricedecimal
- 5.17
- first_sold_atdate & time
- 2009-12-02 14:43:00
- is_merchandisenumber
- 1
- stock_codetext · unique id
- 10080
- descriptiontext
- GROOVY CACTUS INFLATABLE
- category_idnumber · links to categories
- 17
- list_pricedecimal
- 0.39
- first_sold_atdate & time
- 2009-12-02 16:02:00
- is_merchandisenumber
- 1
- stock_codetext · unique id
- 10109
- descriptiontext
- BENDY COLOUR PENCILS
- category_idnumber · links to categories
- 8
- list_pricedecimal
- 0.42
- first_sold_atdate & time
- 2009-12-03 12:31:00
- is_merchandisenumber
- 1
- stock_codetext · unique id
- 10120
- descriptiontext
- DOGGY RUBBER
- category_idnumber · links to categories
- 17
- list_pricedecimal
- 0.21
- first_sold_atdate & time
- 2009-12-01 16:17:00
- is_merchandisenumber
- 1
- stock_codetext · unique id
- 10123C
- descriptiontext
- HEARTS WRAPPING TAPE
- category_idnumber · links to categories
- 8
- list_pricedecimal
- 0.65
- first_sold_atdate & time
- 2009-12-01 14:28:00
- is_merchandisenumber
- 1
first 6 of 5,304 rows · your queries use all of them
solution.sql
Hints and the answer are always available. Using them counts the question as solved with help.