popular item transcation query:

WITH base as (
    SELECT 
        o.o_id, 
        o.o_entry_d,
        c.c_first,
        c.c_middle,
        c.c_last,
        i.i_name,
        ol.ol_quantity, 
        rank() over (partition by o_id order by ol.ol_quantity DESC) AS rank_by_quantity,
        DENSE_RANK() over (partition by i_name order by o_id ASC) + DENSE_RANK() over (partition by i_name order by o_id DESC)  AS orders_with_popular_items,
        DENSE_RANK() over (order by o_id) AS total_cnt
    FROM public.order o
    JOIN public.order_line ol
        ON o.o_id = ol.ol_o_id
    JOIN public.item i 
        ON ol.ol_i_id = i.i_id
    JOIN ( 
        SELECT c_id, c_first, c_middle, c_last
        FROM public.customer
        WHERE c_w_id = {w_id} AND c_d_id = {d_id}
    ) c 
        ON o.o_c_id = c.c_id
    WHERE o.o_d_id = {d_id} AND o.o_w_id = {w_id} AND o.o_id BETWEEN {N} - {L} AND {N} -1
)
select 
    o_id, 
    o_entry_d, 
    c_first, 
    c_middle, 
    c_last, 
    i_name, 
    ol_quantity, 
    orders_with_popular_items * 100.0/total_cnt AS percentage
    from base where rank_by_quantity = 1