create or replace table `temp_eu.delete_stripe_refunds` as ( select * from ( with pre_process as ( SELECT * FROM `reby-cloud.reby_stripe.py_stripe_preprocessed` --where id in (select id from`reby-cloud.temp_eu.delete_stripe` where amount_refunded > 0 ) --where customer = 'cus_GorMSGP2VREHQs' where rn = 1 order by customer,id,rn asc ) select case when refunds.charge is not null and charges.type ='refund' then refunds.balance_transaction else charges.balance_transaction end as balance_transaction, case when refunds.charge is not null and charges.type ='refund' then refunds.created_at else charges.created_at end as created_at, charges.charge_id, charges.type, charges.paid, charges.amount as charge_amount, case when refunds.charge is not null and charges.type ='refund' then refunds.amount else null end as refund_amount, charges.customer, charges.user_id from ( select spp.balance_transaction, s.*, u.id as user_id, sa.service_area from `reby-cloud.reby_stripe.py_stripe_processed` s left join `reby-cloud.analytics_reby_v1_eu.pg_users_json` u on s.customer = u.payment_processor[safe_offset(0)].token left join `reby-cloud.reby_marketing_eu.users_servicearea_first_mv_ride` sa on u.id = sa.user_id left join pre_process spp on spp.id = s.charge_id --order by 1,2,3,4,5,6,7,8,9,10,11,12,13 where date(s.created_at) between '2021-02-01' and '2021-09-03' --order by balance_transaction desc ) as charges left join `reby-cloud.reby_stripe.stripe_refunds_api` refunds on charges.charge_id = refunds.charge --where refunds.status = 'succeeded' ) ) ; select st.*, str.created_at, str.balance_transaction, str.user_id, str.charge_amount from `reby-cloud.temp_eu.delete_stripe_downloaded_transactions` st left join `reby-cloud.temp_eu.delete_stripe_refunds` str on st.balance_transaction_id = str.balance_transaction
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter