Closed
Description
Describe the bug
Snowflake - support exclude
To Reproduce
it('should parse a query with exclude', function () {
const parser = new SqlParserV2()
const query = `
select * exclude (user_id,daily_iap,iap_7d,iap_30d,purchases_cnt),
count(distinct user_id)DAU,
sum(daily_iap)daily_revenue,
sum(iap_7d)D7_revenue,
sum(iap_30d) D30_revenue,
sum(purchases_cnt)daily_TRX
from (
select a.interval_date date,
a.user_id,
is_ftd,
case when lt_purchases_amt=2 then 1 else 0 end is_std,
case when lt_purchases_amt=3 then 1 else 0 end is_ttd,
case when lt_purchases_amt>0 then 1 else 0 end is_payer,
case when daily_iap>0 then 1 else 0 end is_payed_today,
ltv_group,
seniority_bin,
payers_segment,
platform,
Mulitiplier_Status,
current_arena_index,
engagement_group,
CASE
WHEN trophies_cnt<3800 THEN 'Studios'
WHEN trophies_cnt<30000 THEN 'Master_League'
WHEN trophies_cnt>=30000 THEN 'Legends_League'
END AS league,
CASE
WHEN trophies_cnt < 125 THEN '0-125'
WHEN trophies_cnt < 1400 THEN '125-1400'
WHEN trophies_cnt < 2000 THEN '1400-2000'
WHEN trophies_cnt < 3800 THEN '2000-3800'
WHEN trophies_cnt < 7000 THEN '3800-7000'
WHEN trophies_cnt < 16000 THEN '7000-16000'
WHEN trophies_cnt < 30000 THEN '16000-30000'
WHEN trophies_cnt >= 30000 THEN '30000+'
END AS trophy_group,
CASE
WHEN trophies_cnt < 1500 THEN '0-1500'
WHEN trophies_cnt < 3800 THEN '1500-3800'
WHEN trophies_cnt < 8000 THEN '3800-8000'
WHEN trophies_cnt < 17000 THEN '8000-17000'
WHEN trophies_cnt < 30000 THEN '17000-30000'
WHEN trophies_cnt >= 30000 THEN '30000+'
END AS trophy_group2,
case when last10D/10 <2 then '0-2'
when last10D/10 <5 then '2-5'
when last10D/10 <10 then '5-10'
when last10D/10 <20 then '10-20'
else '20+' end Avglast10TrxRevenue,
case when time_between_purchases/10 <=2 then '0-2'
when time_between_purchases/10 <=7 then '2-7'
when time_between_purchases/10 <=14 then '8-14'
when time_between_purchases/10 <=30 then '14-30'
when time_between_purchases/10 <=60 then '30-60'
when time_between_purchases/10 <=90 then '60-90'
when time_between_purchases/10 >90 then '90+' end avg_time_between_purchases,
case when last10Dmatches/10 <=2 then '0-2'
when last10Dmatches/10 <=7 then '3-7'
when last10Dmatches/10 <=12 then '8-12'
else '12+'
end last10Dmatches,
case when iap_30d = 0 then '0'
when iap_30d <= 1 then 'Low (0-1)'
when iap_30d <= 2 then 'Med (1-3)'
when iap_30d <= 5 then 'High (3-8)'
when iap_30d <= 15 then 'Very High (8-15)'
when iap_30d > 15 then 'VIP (15+)'
end ltv_group_30d,
max30D max30_priceP,
days_since_last_purchase,
daily_iap,
iap_7d,
iap_30d,
purchases_cnt
from candivore.prod.daily_users_from_params a
--left join max30D max30 on a.user_id=max30.user_id and interval_date-1=max30.date
left join (
select interval_date,user_id, case
when datediff('day',case when dslp is null then days_since_last_purchase else lag(days_since_last_purchase) over(partition by user_id order by interval_date)+1 end,interval_date)<2 then '0-2 days'
when datediff('day',case when dslp is null then days_since_last_purchase else lag(days_since_last_purchase) over(partition by user_id order by interval_date)+1 end,interval_date)<7 then '2-7 days'
when datediff('day',case when dslp is null then days_since_last_purchase else lag(days_since_last_purchase) over(partition by user_id order by interval_date)+1 end,interval_date)<14 then '7-14 days'
when datediff('day',case when dslp is null then days_since_last_purchase else lag(days_since_last_purchase) over(partition by user_id order by interval_date)+1 end,interval_date)<30 then '14-30 days'
when datediff('day',case when dslp is null then days_since_last_purchase else lag(days_since_last_purchase) over(partition by user_id order by interval_date)+1 end,interval_date)<90 then '30-90 days'
when datediff('day',case when dslp is null then days_since_last_purchase else lag(days_since_last_purchase) over(partition by user_id order by interval_date)+1 end,interval_date)<365 then '90-365 days'
when datediff('day',case when dslp is null then days_since_last_purchase else lag(days_since_last_purchase) over(partition by user_id order by interval_date)+1 end,interval_date)>=365 then '365+ days'
end days_since_last_purchase
from (select *,
max(dslp) over(partition by user_id order by interval_date) days_since_last_purchase
from(
select interval_date,
user_id,
case when daily_iap is null then null else interval_date end DSLP
from candivore.prod.daily_users_from_params
where interval_date>=current_date-360 and lt_purchases_amt>0
) ))DSLP on a.user_id=dslp.user_id and a.interval_date=dslp.interval_date
left join (select user_id,interval_date,last10Dmatches
from(
select interval_date,
user_id,
sum(daily_match_cnt) over (partition by user_id order by interval_date ASC rows BETWEEN 9 PRECEDING AND CURRENT ROW) last10Dmatches
from candivore.prod.daily_users_from_params
where interval_date>=current_date-360
) )L10DMatches on a.user_id=L10DMatches.user_id and a.interval_date=L10DMatches.interval_date
left join (select user_id,
date(derived_tstamp)interval_date,
max(last10D) last10D,
max(time_between_purchases)time_between_purchases
from (
select *, sum(days_diff) over (partition by user_id order by derived_tstamp ASC rows BETWEEN 9 PRECEDING AND CURRENT ROW)time_between_purchases
from(
select user_id,
derived_tstamp,
iap_price,
sum(iap_price) over (partition by user_id order by derived_tstamp ASC rows BETWEEN 9 PRECEDING AND CURRENT ROW)last10D,
count(derived_tstamp) over (partition by user_id order by derived_tstamp ASC rows BETWEEN 9 PRECEDING AND CURRENT ROW)last10D_trx,
datediff('day',lag(derived_tstamp) over(partition by user_id order by derived_tstamp),derived_tstamp)days_diff
from candivore.prod.f_in_app_purchase
where date(derived_tstamp)>=current_Date-360 --and user_id='60f49c2bbc597429a538a9b4'
)
)
where date(derived_tstamp)>=current_Date-360 and last10D_trx=10
group by all)l10DTR on a.user_id=l10DTR.user_id and a.interval_date=l10DTR.interval_date
left join (
select distinct user_id,
case when avg_stakes_matches_share>=percentile_50 then 'multiplier_user'else 'Not_multiplier_user'end Mulitiplier_Status
from
(select distinct user_id,
avg(stakes_matches_share) over (partition by user_id) avg_stakes_matches_share,
avg(stakes_1_share) over (partition by user_id) avg_stakes_1_share,
PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY stakes_matches_share) OVER() AS percentile_50
from
(select
*,
stakes_matches/Total_matches stakes_matches_share,
stakes_matches_1/Total_matches stakes_1_share
from
(
select
distinct date(derived_tstamp) date,
user_id,
count(distinct case when stakes_multiplier=1 then event_id end) over(partition by date(derived_tstamp),user_id) stakes_matches_1,
count(distinct case when stakes_multiplier!=1 then event_id end) over(partition by date(derived_tstamp),user_id) stakes_matches,
count(distinct event_id) over(partition by date(derived_tstamp),user_id) Total_matches
from
candivore.prod.f_user_match
where
date(derived_tstamp) >= '2023-11-16'
)
where Total_matches>=5
and stakes_matches>0
))
) stakes on a.user_id=stakes.user_id
where is_active=1 and is_boot_done=1 and a.interval_date>current_date-300
) a
group by all`
const parsedSql = parser.parse(query, WarehouseType.Snowflake)
expect(parsedSql).toBeDefined()
})