second_approach.sql

Home   »   second_approach.sql

WITH parameters AS (
	SELECT
		CURRENT_DATE AS start_date,
		'validated' AS transation_status_ok,
	  10 AS transaction_count_low,
	  30 AS transaction_count_high,
	  20 AS account_period_days 
)

,grouped AS (
	SELECT
		p.start_date AS day,
		c.id AS customer_id,
	  COUNT(t.amount_euros) AS transaction_count,
	  SUM(t.id) AS transaction_sum_euros
	FROM customers AS c
	CROSS JOIN parameters AS p
	LEFT JOIN transactions AS t ON (
	    t.customer_id = c.id
			AND t.status = p.transation_status_ok
	)
	WHERE TRUE
		AND t.date::DATE 
			BETWEEN (start_date - account_period_days * INTERVAL '1 day')
			AND start_date
	GROUP BY p.start_date, c.id
)

SELECT
	g.day,
	g.customer_id,
	g.transaction_count,
	g.transaction_sum_euros
FROM grouped AS g
CROSS JOIN parameters AS p
WHERE TRUE
	AND g.transaction_count
		BETWEEN p.transaction_count_low
		AND p.transaction_count_high - 1

Leave a Reply

Your email address will not be published. Required fields are marked *