flag and sorting data using the highest sales-CodePudding

I want to create and sorting data based on latest month and highest sales, thats what created flagprod from row 1-4. But the problem is I want to make this flagprod row 1-4 become their product identifier, so any product under 2022-08-01 will refers to flagprod

Row   Month       Product     Sales  Flagprod
1     2022-08-01  product1    150    1
2     2022-08-01  product2    100    2
3     2022-08-01  product4    80     3
4     2022-08-01  product3    50     4
5     2022-07-01  product4    150    
6     2022-07-01  product2    100    
7     2022-07-01  product1    80     
8     2022-07-01  product3    50   
9     2022-06-01  product5    150    
10    2022-06-01  product4    100    
11    2022-06-01  product1    80     
12    2022-06-01  product3    50    
13    2022-06-01  product2    30

the data I want will be like this

Row   Month       Product     Sales  Flagprod
1     2022-08-01  product1    150    1
2     2022-08-01  product2    100    2
3     2022-08-01  product4    80     3
4     2022-08-01  product3    50     4
5     2022-07-01  product4    150    3
6     2022-07-01  product2    100    2
7     2022-07-01  product1    80     1
8     2022-07-01  product3    50     4
9     2022-06-01  product5    150    5
10    2022-06-01  product4    100    3
11    2022-06-01  product1    80     1
12    2022-06-01  product3    50     4
13    2022-06-01  product2    30     2

CodePudding user response：

Consider below approach

with ranks as (
  select Product, rank() over(order by Month desc, Sales desc) as Flagprod 
  from (
    select * from your_table
    qualify 1 = row_number() over(partition by Product order by Month desc)
  )
)
select t.*, Flagprod
from your_table t
join ranks
using (Product)

if applied to sample data in your question - output is

CodePudding user response：

Would you consider below query ?

WITH latest AS (
  SELECT *, RANK() OVER (ORDER BY sales DESC) AS Flagprod,
    FROM sample_table 
   WHERE Month = (SELECT MAX(Month) FROM sample_table)
)
SELECT t.*,
       IFNULL(Flagprod, CAST(REPLACE(Product, 'product','') AS INT64)) AS Flagprod
  FROM sample_table t
  LEFT JOIN latest USING (Product)
 ORDER BY Month DESC, Sales DESC;

Query Results:
 ----- ------------ ---------- ------- ---------- 
| Row |   Month    | Product  | Sales | Flagprod |
 ----- ------------ ---------- ------- ---------- 
|   1 | 2022-08-01 | product1 |   150 |        1 |
|   2 | 2022-08-01 | product2 |   100 |        2 |
|   3 | 2022-08-01 | product4 |    80 |        3 |
|   4 | 2022-08-01 | product3 |    50 |        4 |
|   5 | 2022-07-01 | product4 |   150 |        3 |
|   6 | 2022-07-01 | product2 |   100 |        2 |
|   7 | 2022-07-01 | product1 |    80 |        1 |
|   8 | 2022-07-01 | product3 |    50 |        4 |
|   9 | 2022-06-01 | product5 |   150 |        5 |
|  10 | 2022-06-01 | product4 |   100 |        3 |
|  11 | 2022-06-01 | product1 |    80 |        1 |
|  12 | 2022-06-01 | product3 |    50 |        4 |
|  13 | 2022-06-01 | product2 |    30 |        2 |
 ----- ------------ ---------- ------- ----------