codingisforlosers

SELECT
user_id,
week_start,
week_end,
week_number,
CASE WHEN transactions_created = transactions_created_lifetime and transactions_created > 0 THEN 1 ELSE 0 END as new_flag,
CASE WHEN transactions_created_prev > 0 AND transactions_created = 0 THEN 1 ELSE 0 END as churns_flag,
CASE WHEN transactions_created > 0 AND transactions_created_prev = 0 and transactions_created != transactions_created_lifetime THEN 1 ELSE 0 END as reactivations_flag,
CASE WHEN transactions_created > 0 AND transactions_created_prev > 0 THEN 1 ELSE 0 END as retentions_flag
FROM (

codingisforlosers

SELECT
date_in_range,
date_in_range_bom,
date_in_range_bom_mom,
date_in_range_yoy
FROM
(
	SELECT 
	date_in_range,
	date_in_range_bom,

codingisforlosers

SELECT
date_in_range,
day_number,
week_number,
min(date_in_range) over (partition by week_number) week_start,
max(date_in_range) over (partition by week_number) week_end
FROM (
	SELECT 
	date_in_range,
	date_diff(date_in_range, cast('2019-07-01' as date), DAY)+1 as day_number,