[Uber Freight: Price Elasticity XP] #sql #querybuilder

price_elasticity_XP.sql

/*
- 2021-09-24 Received this from He
*/

with elasticity_xp as 
(select
        msg.load_uuid
        , max_by(msg.elasticity_experiment.pre_xp_price, ts) as pre_xp_price
        , max_by(msg.elasticity_experiment.post_xp_price, ts) as post_xp_price
        , max_by(msg.elasticity_experiment.perturbation_factor, ts) as perturbation_factor
        , max_by(msg.elasticity_experiment.perturbation_scale, ts) as perturbation_scale

from rawdata.Kafka_hp_pricefinder_treatment_log_NoDedup
where true
        and date(datestr) >= date('2021-08-17') -- beginning of the elasticity XP
        and (msg.shadow_info is null or not msg.shadow_info.is_shadowing)
        and (msg.price_type is null or not (msg.price_type = 'FuturePrice'))
        and msg.treatment_result.treatment_group.randomize_by is not null
        and (msg.is_cost_plus_load is null or not msg.is_cost_plus_load)
        and msg.pricing_trigger in ('batch', 'ipa')
        and coalesce(msg.error_message, '') = ''
        and msg.tenancy = 'uber/production'
        and msg.elasticity_experiment.perturbation_scale IS NOT NULL
group by 1
)

select *,
    case
          when perturbation_scale > 0 then 'price_increase'
          when perturbation_scale < 0 then 'price_decrease'
          when perturbation_scale = 0 then 'no_change'
    end as elasticity_xp_treatment
from elasticity_xp