noon_one.sql

Select
country_code,date(subscribed_timestamp) as acq_date,
CASE
WHEN lower(mp) in ('food','noonfood')  then 'food'
WHEN lower(mp) in ('nooninstant')  then 'minutes'
WHEN lower(mp) in ('ecom')  then 'core'
else lower(mp)
end as mp,
count(distinct customer_code) as cx_count
from
(Select
timestamp_add(created_at, interval 4 hour) as subscribed_timestamp,
customer_code,
country_code,
JSON_EXTRACT_SCALAR(misc,"$.tracking.experience") as mp
from `noondwh.loyalty_v2.customer_plan` a
inner join
(SELECT id_plan
FROM noondwh.ref.plan
LEFT JOIN noondwh.ref.tier USING (id_tier)
WHERE country_code IN ("AE")
AND plan_type = 'buy_in'
AND plan_code not like 'test%') b
using (id_plan)
where 1=1
QUALIFY row_number() over (partition by customer_code,a.country_code order by timestamp_add(created_at, interval 4 hour)) = 1)
where date(subscribed_timestamp) >= "2025-01-01"
group by all
having cx_count > 50
order by 1,2 desc, 4 desc