Complex Postgresql query using window functions and cases, building views and materialized views

making_views.sql

CREATE OR REPLACE VIEW public.centiles_by_small_region
AS SELECT s2.small_pid,
    s2.p_region,
    percentile_cont(0.30::double precision) WITHIN GROUP (ORDER BY (mode_prices.mode_price::double precision)) AS percentile_30,
    percentile_cont(0.60::double precision) WITHIN GROUP (ORDER BY (mode_prices.mode_price::double precision)) AS percentile_60,
    percentile_cont(0.90::double precision) WITHIN GROUP (ORDER BY (mode_prices.mode_price::double precision)) AS percentile_90
   FROM mode_prices
     JOIN ( SELECT product_id_sml_class.product_id,
            product_id_sml_class.small_pid,
                CASE
                    WHEN (product_id_sml_class.product_id / 1000000) = 1 THEN 'region1'::text
                    WHEN (product_id_sml_class.product_id / 1000000) = 2 THEN 'region2'::text
                    WHEN (product_id_sml_class.product_id / 1000000) = 3 THEN 'region3'::text
                    WHEN (product_id_sml_class.product_id / 1000000) = 4 THEN 'region4'::text
                    WHEN (product_id_sml_class.product_id / 1000000) = 5 THEN 'region5'::text
                    WHEN (product_id_sml_class.product_id / 1000000) = 6 THEN 'region6'::text
                    ELSE NULL::text
                END AS p_region
           FROM product_id_sml_class) s2 USING (product_id)
  GROUP BY s2.small_pid, s2.p_region;
 
CREATE MATERIALIZED VIEW product_pricetags AS
SELECT 
	product_id,
	small,
	small_pid,
	CASE
		WHEN mode_price < percentile_30 THEN 1
		WHEN mode_price < percentile_60 THEN 2
		WHEN mode_price < percentile_90 THEN 3
		ELSE 4
	END AS price_tag
FROM (
	SELECT 
		product_id,
	    mode() WITHIN GROUP (ORDER BY receipt_item.item_sell_price) AS mode_price
	FROM receipt_item
	JOIN receipts using(receipt_id)
	WHERE receipt_timestamp > NOW() - '3 months'::INTERVAL
	GROUP BY 1
) s1
JOIN (
	SELECT
		product_id,
		small,
		small_pid,
		CASE
			when product_id/1000000 = 1 then 'region1'
			when product_id/1000000 = 2 then 'region2'
			when product_id/1000000 = 3 then 'region3'
			when product_id/1000000 = 4 then 'region4'
			when product_id/1000000 = 5 then 'region5'
			when product_id/1000000 = 6 then 'region6' 
			else null 
		end as p_region
	FROM product_id_sml_class
) s2 USING(product_id)
JOIN centiles_by_small_region USING (small_pid, p_region);