Data Engineer

Toon het aantal bestellingen en "producten van de benodigde categorie in de bestelling" voor 2021 in de categorieën "Kleding" en voor 2022 in de categorie "Schoenen" (eindweergave: 2 regels. Velden: jaar, aantal producten, aantal bestellingen) with cte as( select year(o.order_date) as year_orders, g.cat_1, count(distinct o.order_id) as count_orders, sum(g.quantity) as count_quan from Orders o join Goods g on o.order_id = g.order_id where (g.cat_1 = 'Kleding' and Year(o.order_date) = 2021) or (g.cat_1 = 'Schoenen' and Year(o.order_date) = 2022) group by year_orders, g.cat_1 ) select year_orders, count_quan, count_orders from cte voor 2025, top 3 in elke categorie op basis van verkoophoeveelheid model with cte as( select cat_1, price, quantity, model, rank() over(partition by cat_1) as rk from Orders o join Goods g on o.order_id = g.order_id where Year(o.order_date) = 2025 ) voor 2025, top 3 in elke categorie op basis van verkoopbedrag model with cte as( select g.cat_1, g.model, (price * quantity), rank() over(partition by g.cat_1) as rk from Orders o join Goods g on o.order_id = g.order_id where Year(o.order_date) = 2025 ) with cte as( select g.cat_1, g.model, sum(price * quantity) as sum_, rank() over(partition by g.cat_1 order by sum() desc) as rk from Orders o join Goods g on o.order_id = g.order_id where Year(o.order_date) = 2025 ) with cte1 as( select g.cat_1, g.model, sum(price * quantity) as sum_total, from Orders o join Goods g on o.order_id = g.order_id where Year(o.order_date) = 2025 ), cte2 as(select g.cat_1, g.model, sum_total, rank() over(partition by g.cat_1 order sum_total desc) as rk from cte1 ) select g.cat_1, g.model, sum_total from cte2 where rk <=3 ken de naam van de categorie, model en de totale verkoop with cte1 as( select g.cat_1 as cat_name, g.model as model, sum(price * quantity) as sum_total, from Orders o join Goods g on o.order_id = g.order_id where Year(o.order_date) = 2025 group by Year(o.order_date), g.model ), cte2 as(select cat_name, model, sum_total, rank() over(partition by cat_name order sum_total desc) as rk from cte1 ) select cat_name, model, sum_total from cte2 where rk <=3

1. Bestellingen: - order_date datetime NIET NULL - user_id int NIET NULL - order_id int NIET NULL 2. Artikelen: - order_id int NIET NULL - model int NIET NULL - cat_1 varchar(50) NIET NULL - price int NIET NULL - quantity int NIET NULL Waarden in cat_1: - Kleding - Schoeisel - Cosmetica - Huishoudartikelen - Speelgoed Toon het aantal bestellingen en "aantal benodigde producten in de categorie in de bestelling" voor 2021 in de categorieën "Kleding" en voor 2022 in de categorie "Schoeisel" (uiteindelijke weergave: 2 rijen. Velden: jaar, aantal producten, aantal bestellingen) with cte as( select year(o.order_date) as year_orders, g.cat_1, count(distinct o.order_id) as count_orders, sum(g.quantity) as count_quan from Orders o join Goods g on o.order_id = g.order_id where g.cat_1 = 'Kleding' and Year(o.order_date) = 2021 or g.cat_1 = 'Schoeisel' and Year(o.order_date) = 2022 group by year_orders, g.cat_1 ) select year_orders, count_quan, count_orders from cte

Waarom heeft u besloten de ranking in een aparte CTE te plaatsen? Waarom kon het niet direct in de eerste CTE worden gebruikt?