Untitled

WITH swipes AS (
	SELECT
		"accountId",
		"resultGivenAt"::date as swiped_at,
		COUNT(*) as swipes
	FROM "CardDistributions"
	WHERE created_at > '2023-07-01 00:00:00.000+00' AND "result" IS NOT NULL AND level > 0
	GROUP BY 1, 2
)
SELECT
	"accountId",
	SUM(swipes) as total_swipes,
	AVG(swipes) as average_per_day,
	COUNT(*) as days_with_swipe,
	COUNT(*)::float * 100 / (SELECT NOW()::date - MIN(swiped_at)::date FROM swipes) as days_percentage
FROM swipes
GROUP BY 1
ORDER BY 4 DESC;