Hopefully the title can make sense to somebody, so I can get somebody working with a database (BigQuery in my case) to look into into.
Given two quarters, I need to find out the # of orders placed by each consumer for each of the two. I need also to count how many consumer I have in Q3 vs Q4 and do some calculation on the consumer retention.
I wish I could obtain the majority of this numbers with just one query, thus avoiding a number of queries.
I'll paste below a query in the attempt to explain what I need
SELECT T1.email , T2.email
FROM
(
SELECT email, 1 AS KEY
FROM [reporting.order]
WHERE 1 = 1
AND timestampOrdered > TIMESTAMP_TO_USEC(TIMESTAMP("2014-07-01 05:00:00"))
AND timestampOrdered < TIMESTAMP_TO_USEC(TIMESTAMP("2014-10-01 05:00:00"))
AND orderStatus IN ('Delivered', 'Collected', 'PaymentCaptured')
AND name NOT LIKE '%test%'
AND restaurantId NOT LIKE '%test%'
GROUP BY email
) T1
JOIN (
SELECT email, 1 AS KEY
FROM [reporting.order]
WHERE 1 = 1
AND timestampOrdered > TIMESTAMP_TO_USEC(TIMESTAMP("2014-10-01 05:00:00"))
AND timestampOrdered < TIMESTAMP_TO_USEC(TIMESTAMP("2015-01-01 05:00:00"))
AND orderStatus IN ('Delivered', 'Collected', 'PaymentCaptured')
AND name NOT LIKE '%test%'
AND restaurantId NOT LIKE '%test%'
GROUP BY email
) T2
ON T1.KEY = T2.KEY
By adding an outer SELECT
, with the above results I was able to count how many emails are in Q4 that were in Q3 (SELECT COUNT(CASE WHEN (T1.Email = T2.Email) THEN 1 ELSE NULL END) AS InPrevQ FROM
) but anything more than this.
I thought to use the WINDOW
function, but I'm struggling to understand its usage.