console_72.sql

-- Сравнение продаж текущий год с предыдущим

SELECT
    -- Категории
       (
       SELECT string_agg(c.title, ' / ')
       FROM hm_taxonomies_category_product c_p
              JOIN hm_taxonomies_categories c ON c_p.category_id = c.id
       WHERE c_p.product_id = s.product_id -- todo: order
       ) AS categories,

--        (SELECT date_part('year', CURRENT_DATE)) as year,

       SUM(retail_total - product_discount_amount) FILTER (WHERE t.year = date_part('year', CURRENT_DATE)) AS total_current,

       SUM(retail_total - product_discount_amount) FILTER (WHERE t.year = date_part('year', CURRENT_DATE) - 1) AS total_prev,

--        (SUM(retail_total - product_discount_amount) FILTER (WHERE t.year = date_part('year', CURRENT_DATE)) / SUM(retail_total - product_discount_amount) OVER ()) AS ratio,

        (
           CASE
             -- a < b = ((b-a)/a) * 100
               WHEN SUM(retail_total - product_discount_amount) FILTER (WHERE t.year = date_part('year', CURRENT_DATE) - 1) < SUM(retail_total - product_discount_amount) FILTER (WHERE t.year = date_part('year', CURRENT_DATE))
                 THEN  ((SUM(retail_total - product_discount_amount) FILTER (WHERE t.year = date_part('year', CURRENT_DATE)) - SUM(retail_total - product_discount_amount) FILTER (WHERE t.year = date_part('year', CURRENT_DATE) - 1))
                            / SUM(retail_total - product_discount_amount) FILTER (WHERE t.year = date_part('year', CURRENT_DATE))) * 100
            -- a > b = ((a-b)/a) * 100
               WHEN SUM(retail_total - product_discount_amount) FILTER (WHERE t.year = date_part('year', CURRENT_DATE) - 1) > SUM(retail_total - product_discount_amount) FILTER (WHERE t.year = date_part('year', CURRENT_DATE))
                 THEN  ((SUM(retail_total - product_discount_amount) FILTER (WHERE t.year = date_part('year', CURRENT_DATE) - 1) - SUM(retail_total - product_discount_amount) FILTER (WHERE t.year = date_part('year', CURRENT_DATE)))
                            / SUM(retail_total - product_discount_amount) FILTER (WHERE t.year = date_part('year', CURRENT_DATE))) * -100
             ELSE 0
               END
        ) AS difference_year_to_year

FROM

    mat_view_sales s
    JOIN view_times t ON s.time_id = t.id

--     JOIN (
--          SELECT
--                 t.year as year,
--                 SUM(retail_total - product_discount_amount) AS sales_total
--          FROM mat_view_sales summ
--                 JOIN view_times t ON summ.time_id = t.id
--         GROUP BY year
--
--     ) summ ON summ.year = t.year


WHERE order_process != 'cancelled'
GROUP BY
         categories