CTE query to find top selling products

 WITH MonthlySales AS (

    SELECT

        p.product_name,

        s.store_id,

        SUM(s.units_sold) as units_sold,

        SUM(s.units_sold) OVER (PARTITION BY p.product_id) as total_units,

        RANK() OVER (PARTITION BY p.product_id ORDER BY SUM(s.units_sold) DESC) as store_rank

    FROM

        product p

    JOIN

        sales s ON p.product_id = s.product_id

    WHERE

        YEAR(p.launch_date)=2022

        AND s.sale_date BETWEEN p.launch_date AND DATE_ADD(p.launch_date, INTERVAL 1 MONTH)

    GROUP BY

        p.product_name, s.store_id

)

SELECT

    ms.product_name,

    st.location_city,

    st.location_country,

    ms.units_sold,

    ms.total_units,

    ROUND(100.0 * ms.units_sold / ms.total_units, 2) as store_influence_percentage

FROM

    MonthlySales ms

JOIN

    stores st ON ms.store_id = st.store_id

WHERE

    ms.store_rank = 1

ORDER BY

    store_influence_percentage DESC;