Describe the bug
Snowflake - should parse a query with unpivot
Database Engine
Snowflake
To Reproduce
it('should parse a query with unpivot', function () {
const parser = new SqlParserV2()
const query = `
select transaction_date,
platform,
date_granularity,
transaction_type,
buyer_country,
sku,
transaction_currency,
exchange_rate,
transaction_amount_usd,
transaction_amount_local_currency,
transaction_amount_ils
from
((select 'android' as platform,
'Daily' as date_granularity,
transaction_date,
transaction_type,
buyer_country,
sku,
transaction_currency,
exchange_rate,
sum(transaction_amount_usd) as transaction_amount_usd,
sum(transaction_amount_local_currency) as transaction_amount_local_currency,
sum(amount_merchant_currency) as transaction_amount_ils
from
(select
to_date(ge.transaction_date, 'Mon DD, YYYY') as transaction_date
,ge.transaction_type as transaction_type
,ge.product_title as product
,ge.sku_id as sku
,ge.buyer_country as buyer_country
,ge.buyer_currency as transaction_currency
,er.rate as exchange_rate
,ge.amount_buyer_currency as transaction_amount_local_currency
,ge.amount_buyer_currency / er.rate as transaction_amount_usd
,ge.amount_merchant_currency as amount_merchant_currency
from (select distinct * from staging.raw.google_play_store_earnings) as ge
join F_EXCHANGE_RATES as er
on date(er.time)= to_date(ge.transaction_date, 'Mon DD, YYYY') and er.currency = ge.buyer_currency)
group by all)
UNION ALL
(select platform,
date_granularity,
transaction_date,
transaction_type,
buyer_country,
sku,
transaction_currency,
exchange_rate,
transaction_amount_usd,
case when transaction_type='CHARGE' then transaction_amount_local_currency when transaction_type='TAXES_AND_FEES' then Taxes_and_Fees_local_currency end as transaction_amount_local_currency,
transaction_amount_usd*ils_exchange_rate as transaction_amount_ils
from
(select distinct *
from
(select 'ios' as platform,
date_granularity,
date as transaction_date,
buyer_country,
sku,
transaction_currency,
exchange_rate,
ils.exchange_rate as ils_exchange_rate,
sum(transaction_amount_usd) as Charge,
sum(developer_proceeds_usd) as Taxes_and_Fees,
sum(transaction_amount_local_currency) as transaction_amount_local_currency,
sum(developer_proceeds) as Taxes_and_Fees_local_currency
from
(select
date(ge1.begin_date) as date
,ge1.date_granularity as date_granularity --if granulity is grater than Daily will convert based on begin_date exchange rate
,ge1.title as sku
,ge1.country_code as buyer_country
,ge1.currency_of_proceeds as transaction_currency
,er1.rate as exchange_rate
,ge1.units as units
,(ge1.customer_price-ge1.developer_proceeds) * ge1.units * (-1) as developer_proceeds
,ge1.customer_price*ge1.units as transaction_amount_local_currency
,ge1.customer_price*ge1.units / er1.rate as transaction_amount_usd
,(ge1.customer_price-ge1.developer_proceeds) * ge1.units / er1.rate * (-1) as developer_proceeds_usd
from (select distinct * from staging.raw.apps_store_connect_sales) as ge1
join (select distinct * from F_EXCHANGE_RATES) as er1
on date(er1.time) = date(ge1.begin_date) and er1.currency = ge1.currency_of_proceeds)
left join
(select date(time) as date, currency, rate as exchange_rate from (select distinct * from F_EXCHANGE_RATES) where currency='ILS') as ils
using(date)
group by all)
unpivot(transaction_amount_usd FOR transaction_type IN (Charge, Taxes_and_Fees)))))`
const parsedSql = parser.parse(query, WarehouseType.Snowflake)
expect(parsedSql).toBeDefined()
})
Describe the bug Snowflake - should parse a query with unpivot
Database Engine Snowflake
To Reproduce