parse complex query t

[jorgemgomes]

Could you please help me to configure sqlparse to get source table, target table, joins and other relevant dependencies from this query? as you can see there are some ctes that are not identified by WITH. how can i achieve this? thanks!

insert into eu_pay_and_ship_stg.fact_redeemed_rewards
with activated_rewards as (
select
id as reward_activation_id,
expires_at,
reward_type,
source as reward_source,
quest_id
from livesync.cee_deliverytwo_transaction_rewards
),

used_rewards as (
select
*,
row_number() over (partition by order_id order by operation_timestamp) as rn
from livesync.cee_deliverytwo_transaction_order_reward_use
),

used_rewards_join as (
select
t1.order_id as order_id,
ar.reward_activation_id as first_reward_activation_id,
ar2.reward_activation_id as second_reward_activation_id,
t1.id as first_reward_redemption_id,
t2.id as second_reward_redemption_id,
ar.reward_type as first_reward_type,
ar2.reward_type as second_reward_type,
ar.reward_source as first_reward_source,
ar2.reward_source as second_reward_source,
ar.quest_id as quest_id,
t1.rn as rn
from used_rewards as t1
inner join activated_rewards as ar on t1.reward_id = ar.reward_activation_id
left join used_rewards as t2 on t1.order_id = t2.order_id and t1.rn = 1 and t2.rn = 2
left join activated_rewards as ar2 on t2.reward_id = ar2.reward_activation_id
where t1.rn = 1
),

df as (
select
t.category_sk,
delivery_provider_nk,
trunc(date_trunc('day', transaction_created)) as date_day,
count(1) as df_transactions,
percentile_cont(0.5) within group (order by delivery_price_gross) as median_df
from eu_pay_and_ship_stg.fact_delivery_transactions as t
inner join eu_bi.dim_categories as cat on t.category_sk = cat.category_sk
where
lower(t.country_nk) = 'pl'
and delivery_price_gross is not null
and delivery_price_gross > 0
group by 1, 2, 3
),

sf as (
select
*,
row_number() over (partition by ad_id, user_id order by operation_timestamp desc) as rn
from livesync.cee_ps_monetisation_fees
),

cte as (
select distinct
t.order_id,
transaction_nk as order_external_id_char,
buyer_sk,
buyer_nk,
country_nk,
transaction_created,
transaction_completed,
t.category_sk,
first_reward_activation_id,
second_reward_activation_id,
first_reward_redemption_id,
second_reward_redemption_id,
first_reward_type,
second_reward_type,
first_reward_source,
second_reward_source,
case when price_discounted_value is not null then true else false end as sf_discounted,
price_value / 100.00 as sf_real,
coalesce(price_discounted_value, price_value) / 100.00 as sf_paid,
coalesce(delivery_provider_cost_net + speditor_cost_net, 1) as df_olx_cost,
median_df as df_bl_cost,
delivery_price_gross as df_paid,
order_ad_price as item_price
from eu_pay_and_ship_stg.fact_delivery_transactions as t
left join df on df.category_sk = t.category_sk
and date_trunc('day', transaction_created) = date_day
and df.delivery_provider_nk = t.delivery_provider_nk
inner join used_rewards_join as rew on rew.order_id = t.order_id
inner join sf on lower(country_nk) = 'pl' and user_id = t.buyer_nk and listing_nk = ad_id and sf.rn = 1
left join odyn_deliveries.delivery_costs as c on t.site_sk = c.site_sk
and t.delivery_provider_nk = c.delivery_provider_nk
and t.package_weight = c.package_weight
and t.package_size = c.package_size
and t.speditor = c.speditor
and t.transaction_created between c.valid_from_local and coalesce(c.valid_to_local, '2999-12-31')
)

select *
from cte