Building off a general SQL question I already asked using online SQLite editor here, if I have the following table:

how would I get the output to be this:

In the table directly above, weeks 1-2 are assigned to item1 since the total sales for that time period were highest for item1. Same with weeks 3-4, assigned to item2 since that item's total sales were the highest for that time period.

Some code I have been working on is as follows:

SELECT item, 
    SUM(sales) OVER (
          PARTITION BY week order by item) AS total_sales
FROM TABLE1
GROUP BY item;

CASE was suggested to get the "range" for week the item sold the most out of all items, but this populates the items with the same week range. With case added, code is this:

SELECT item,
       CASE 
         WHEN week <= 2 THEN '1-2'
         WHEN week <= 4 THEN '3-4'
         WHEN week <= 5 THEN '5'
         ELSE '>5'
       END weeks,
       SUM(sales) OVER(partition by week order by item) as total_sales
FROM table1
GROUP BY item;

Here is a demo

The above code outputs something similar to:

which is wrong as only item1 was the top seller during weeks 1-2.

I really am not sure how one would get the week(s) for an item when it was the top seller. Again, item1 would have just "Week 1-2" populated once, item2 would have just "Week 3-4" populated once as those were their highest selling weeks.

Everything else I have done just fine. Any guidance or help is very much appreciated.

CodePudding user response：

I think if you just add a row_number() function to implement your business logic and then pick the rows where the row_number() = 1.

here is the fiddle https://www.db-fiddle.com/f/oWBDK3xTSgiCMaw7MS5XKm/0

and here is the fiddle with your demo data https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=62450660190d7f161f5790d188736260

with t as (
SELECT item,
       CASE 
         WHEN week <= 2 THEN '1-2'
         WHEN week <= 4 THEN '3-4'
         WHEN week <= 5 THEN '5'
         ELSE '>5'
       END weeks,
       SUM(sales) as total_sales
FROM table1
GROUP BY item, 
  CASE 
         WHEN week <= 2 THEN '1-2'
         WHEN week <= 4 THEN '3-4'
         WHEN week <= 5 THEN '5'
         ELSE '>5'
       END ),
  t2 as (
  select item, weeks, total_sales,
  row_number() over (partition by weeks order by total_sales desc) rn  from t)
  select * from t2 where rn = 1 order by weeks

CodePudding user response：

You need 2 levels of aggregation:

SELECT item, weeks, MAX(total_sales) total_sales
FROM (
  SELECT item,
         CASE 
           WHEN week <= 2 THEN '1-2'
           WHEN week <= 4 THEN '3-4'
           WHEN week <= 5 THEN '5'
           ELSE '>5'
         END weeks,
         SUM(sales) total_sales
  FROM table1
  GROUP BY item, weeks
)
GROUP BY item;

The outer query uses SQLite's feature of Bare columns in an aggregate query and returns the row with the max value of total_sales.

See the demo.