SQl query to show the most popular category in terms of sales quantity for each country and each gender in year XXXX

There are Three tables here Orders, Customers and Items.

create table Orders(order_id varchar(20), order_datetime datetime,
customer_id varchar(10), item_id varchar(20), amount int, quantity
int, age int);

insert into Orders values('a01' ,'2020-01-01 7:23:59', 'b27', 'c87',
20, 2 , 19);

insert into Orders values('a02', '2020-01-02 8:13:27', 'b33', 'c56',
100, 3, 15);

insert into Orders values('a02', '2020-01-02 8:13:27', 'b33', 'c57',
39,1, 15);

insert into Orders values('a03', '2020-01-02 13:26:39', 'b42', 'c31',
587,10, 19);


create table Customer(customer_id varchar(20), name varchar(20),
gender varchar(10), country varchar(20), age int);

insert into Customer values('b27' ,'sam', 'm', 'Singapore', 20);

insert into Customer values('b28' ,'bob', 'm', 'Indonesia', 27);

insert into Customer values('b29' ,'julie', 'f', 'Korea', 43);


create table Items(item_id varchar(20), category varchar(20));

insert into Items values('c87','sportswear');

insert into Items values('c56','skincare');

insert into Items values('c57','food');

Please tell me how to solve this. I have tried few combinations but were not successful. Thanks in advance.

Answer

In Mysql 8 you have ROW_NUMBER to select the “best” from a PARTITION

SELECT country,gender, category,oYear, total
FROM
  (SELECT country,gender, category,oYear, total,
      ROW_NUMBER() OVER(PARTITION BY country,gender, category,oYear ORDER BY  total DESC) rn
      FROM
          (SELECT c.country,c.gender, SUM(o.amount * o.quantity) total, i.category,YEAR(o.order_datetime) oYear
          FROM Orders o INNER JOIN Customer c ON o.customer_id = c.customer_id
          INNER JOIN Items i ON o.item_id = i.item_id
          GROUP BY  c.country,c.gender, i.category,YEAR(o.order_datetime)
          ) t1 
  ) t2
WHERE rn = 1

country   | gender | category   | oYear | total
:-------- | :----- | :--------- | ----: | ----:
Indonesia | m      | food       |  2020 |    39
Indonesia | m      | skincare   |  2020 |   300
Singapore | m      | sportswear |  2020 |    40

db<>fiddle here