select product, count(*) as orders, sum(quantity) as quantity, round(100 * sum(quantity) / sum(sum(quantity)) over (), 1) as share_pct from orders group by product order by quantity desc;


select date_trunc('month', date) as month, count(*) as orders, sum(quantity) as quantity from orders group by month order by month;


select u.username, u.name, count(*) as orders, sum(o.quantity) as quantity from orders o
join users u on o.user_uuid = u.uuid group by all order by quantity desc limit 5;


select (date_diff('year', u.birthdate, date '2020-01-01') // 20) * 20 as age_group, count(distinct u.uuid) as users, count(*) as orders, round(avg(o.quantity), 2) as avg_quantity from orders o
join users u on o.user_uuid = u.uuid group by age_group order by age_group;


with daily as (select date::date as day, sum(quantity) as quantity from orders group by day)
select day, quantity, sum(quantity) over (order by day) as cumulative, round(avg(quantity) over (order by day rows between 6 preceding and current row), 1) as avg_7d from daily order by day limit 10;


select strftime(date, '%Y-%m') as month, product, sum(quantity) as quantity from orders group by month, product qualify rank() over (partition by month order by sum(quantity) desc) = 1 order by month;

pivot (select strftime(date, '%Y-%m') as month, product, quantity from orders) on product using sum(quantity) order by month;