-- exo 1
select round(count(*) * 1.0 / count(distinct user_uuid), 2) as avg_orders_per_user, round(avg(quantity), 2) as avg_quantity_per_order from orders;

-- exo 2
select u.username, min(o.date) as first_order, max(o.date) as last_order, date_diff('day', min(o.date), max(o.date)) as days_between from orders o
join users u on o.user_uuid = u.uuid group by all order by u.username;

-- exo 3
select hour(date) as hour_of_day, sum(quantity) as quantity from orders group by hour_of_day 
qualify rank() over (order by sum(quantity) desc) = 1;

-- exo 4
with monthly as (select product, date_trunc('month', date) as month, sum(quantity) as quantity from orders group by all)
select product, strftime(month, '%Y-%m') as month, quantity, round(100 * (quantity - lag(quantity) over w) / lag(quantity) over w, 1) as mom_pct from monthly window w as (partition by product order by month) order by product, month;

-- exo 5
select u.username, u.name, count(distinct o.product) as products from orders o
join users u on o.user_uuid = u.uuid group by all having count(distinct o.product) = (select count(distinct product) from orders) order by u.username;