average_ordering_rate.sql

with menu_item_order_counts as (
    select o.id             as order_id,
           o.menu_id,
           oi.menu_item_id,
           sum(oi.quantity) as quantity
    from orders o
             join order_items oi on o.id = oi.order_id
             join menu_items mi on oi.menu_item_id = mi.id
             join menu_groups mg on mi.menu_group_id = mg.id
    where o.menu_id is not null
      and o.state = 'closed'
      and not mg.is_cutlery
    group by o.id, o.menu_id, oi.menu_item_id
    order by o.id
),
     menu_item_ordering_rates as (
         select *,
                sum(mioc.quantity) over (partition by order_id)                 as order_total,
                mioc.quantity / sum(mioc.quantity) over (partition by order_id) as rate
         from menu_item_order_counts mioc
     )

select menu_item_id, avg(rate)
from menu_item_ordering_rates
group by menu_item_id

average_rate.csv

order_rate_results.csv

ordering_rate.sql

with menu_item_order_counts as (
    select o.id             as order_id,
           o.menu_id,
           oi.menu_item_id,
           sum(oi.quantity) as quantity
    from orders o
             join order_items oi on o.id = oi.order_id
             join menu_items mi on oi.menu_item_id = mi.id
             join menu_groups mg on mi.menu_group_id = mg.id
    where o.menu_id is not null
      and o.state = 'closed'
      and not mg.is_cutlery
    group by o.id, o.menu_id, oi.menu_item_id
    order by o.id
),
     menu_item_ordering_rates as (
         select *,
                sum(mioc.quantity) over (partition by order_id)                 as order_total,
                mioc.quantity / sum(mioc.quantity) over (partition by order_id) as rate
         from menu_item_order_counts mioc
     )

select *
from menu_item_ordering_rates;