Skip to content

Instantly share code, notes, and snippets.

@un1ko85
Created May 20, 2020 15:37
Show Gist options
  • Save un1ko85/061cf58081ab4b0100ce6d27f57393e2 to your computer and use it in GitHub Desktop.
Save un1ko85/061cf58081ab4b0100ce6d27f57393e2 to your computer and use it in GitHub Desktop.
-- Сравнение продаж текущий год с предыдущим
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment