Untitled
unknown
plain_text
8 months ago
17 kB
0
Indexable
Never
begin
create temporary table target_values as (
with target2023 as (
select
cast(skb.yearmonth as date) as month_dt,
case
when skb.country in ('AU', 'NZ') then 'ANZ'
when skb.country in ('BE', 'LU') then 'BEL'
else skb.country
end as country,
case
when skb.country in ('AT', 'BE', 'LU', 'CH', 'DE', 'DK', 'NL', 'PL', 'SK')
then 'North'
when skb.country in ('GB', 'IE')
then 'UKI'
when skb.country in ('AU', 'NZ', 'BG', 'ES', 'FR', 'IL', 'IT')
then 'South'
else 'Other' end as segment,
coalesce(
case
when skb.chain in ('Non-Chain', 'non-chain') then 'SMB'
when skb.chain in ('Chain', 'chain') then 'SA'
end, 'No data') as sales_pillar,
coalesce(skb.partner_type, 'No data') as partner_type,
'Not Specified' as order_bucket,
case
when skb.kpi_name = 'Offline Partners'
then 'Net Churn'
else skb.kpi_name
end as metric_name,
'Target' as value_type,
sum(cast(skb.metric_value as decimal)) as value
from `just-data.production_reference_sales_analytics.sales_kpi_budget_2022` as skb
where skb.yearmonth >= '2023-01-01'
and cast(skb.yearmonth as date) < '2024-01-01'
and skb.kpi_name in ('New Partners', 'Online Partners', 'Offline Partners', 'Orders')
and skb.metric_name = 'Country Input'
and skb.partner_type in ('Restaurant', 'Grocery')
group by 1, 2, 3, 4, 5, 6, 7
order by 1, 2, 3, 4, 5, 6, 7),
target2024 as (
select
cast(skb.yearmonth as date) as month_dt,
case
when skb.country in ('BE', 'LU') then 'BEL'
when skb.country in ('NZ', 'AU') then 'ANZ'
else skb.country
end as country,
case
when skb.country in ('AT', 'BE', 'LU', 'CH', 'DE', 'DK', 'NL', 'PL', 'SK')
then 'NE'
when skb.country in ('GB', 'IE')
then 'UKI'
when skb.country in ('AU', 'NZ', 'BG', 'ES', 'FR', 'IL', 'IT')
then 'SEANZ'
else 'Other'
end as segment,
coalesce(
case
when skb.chain in ('Non-Chain', 'non-chain') then 'SMB'
when skb.chain in ('Chain', 'chain') then 'SA'
end, 'No data') as sales_pillar,
coalesce(skb.partner_type, 'No data') as partner_type,
'Not Specified' as order_bucket,
case
when skb.kpi_name = 'Offline Partners' then 'Net Churn'
else skb.kpi_name end as metric_name,
'Target' as value_type,
sum(coalesce(cast(skb.metric_value as decimal), 0)) as value
from `just-data.production_reference_sales_analytics.sales_kpi_budget_2022` as skb
where skb.yearmonth >= '2024-01-01'
and cast(skb.yearmonth as date) < date_trunc(cast(current_date as date), month)
and skb.kpi_name in ('New Partners', 'Online Partners', 'Offline Partners', 'Orders')
and skb.metric_name = 'Budget'
and skb.partner_type in ('Restaurant', 'Grocery')
group by 1, 2, 3, 4, 5, 6, 7
order by 1, 2, 3, 4, 5, 6, 7),
target2022 as (
select cast(skb.yearmonth as date) as month_dt,
case
when skb.country in ('BE', 'LU') then 'BEL'
when skb.country in ('NZ', 'AU') then 'ANZ'
else skb.country
end as country,
case
when skb.country in ('AT', 'BE', 'LU', 'CH', 'DE', 'DK', 'NL', 'PL', 'SK')
then 'NE'
when skb.country in ('GB', 'IE')
then 'UKI'
when skb.country in ('AU', 'NZ', 'BG', 'ES', 'FR', 'IL', 'IT')
then 'SEANZ'
else 'Other'
end as segment,
coalesce(
case
when skb.chain in ('Non-Chain', 'non-chain') then 'SMB'
when skb.chain in ('Chain', 'chain') then 'SA'
end, 'No data') as sales_pillar,
coalesce(skb.partner_type, 'No data') as partner_type,
'Not Specified' as order_bucket,
case
when skb.kpi_name = 'Offline Partners' then 'Net Churn'
else skb.kpi_name end as metric_name,
'Target' as value_type,
sum(coalesce(cast(skb.metric_value as decimal), 0)) as value
from `just-data.production_reference_sales_analytics.sales_kpi_budget_2022` as skb
where skb.yearmonth >= '2022-01-01'
and cast(skb.yearmonth as date) < '2023-01-01'
and skb.kpi_name in ('New Partners', 'Online Partners', 'Offline Partners', 'Orders')
and skb.metric_name = 'RFC 10+14'
group by 1, 2, 3, 4, 5, 6, 7
order by 1, 2, 3, 4, 5, 6, 7)
select * from target2022
union all (select * from target2023)
union all (select * from target2024));
create temporary table orders_partner_id as (
with
ttl_rp_orders as (
select date_trunc(cast(frds.snapshot_dt as date), month) as month_dt,
frds.restaurantid as restaurant_id,
sum(coalesce(frds.nr_of_orders, 0)) as orders,
sum(coalesce(frds.total_gmv, 0)) as gmv
from `just-data-warehouse.dwh.fact_restaurant_daily_snapshot` as frds
where frds.snapshot_dt >= '2022-06-01'
group by 1, 2),
orders_dta_temp as (
select tro.restaurant_id as restaurant_id,
tro_temp.month_dt_temp as month_dt,
sum(tro.orders) as orders_3month,
sum(tro.gmv) as gmv_3month
from ttl_rp_orders as tro
cross join (select distinct month_dt as month_dt_temp from ttl_rp_orders) as tro_temp
where tro_temp.month_dt_temp > tro.month_dt
and date_diff(tro_temp.month_dt_temp, tro.month_dt, month) <= 3
and tro_temp.month_dt_temp >= '2022-06-01'
group by 1, 2)
select odt.restaurant_id as restaurant_id,
odt.month_dt as month_dt,
odt.orders_3month as orders_3month,
odt.gmv_3month as gmv_3month
from orders_dta_temp as odt);
create temporary table achieved_values as (
with t as (
with achieved_churn as (
select
date_trunc(cast(cndm.status_change_date as date), month) as month_dt,
case
when dr.country in ('AU', 'NZ') then 'ANZ'
when dr.country in ('BE', 'LU') then 'BEL'
else dr.country
end as country,
case
when dr.country in ('AT', 'BE', 'LU', 'CH', 'DE', 'DK', 'NL', 'PL', 'SK')
then 'NE'
when dr.country in ('GB', 'IE')
then 'UKI'
when dr.country in ('AU', 'NZ', 'BG', 'ES', 'FR', 'IL', 'IT')
then 'SEANZ'
else 'Other' end as segment,
case
when dr.chain = '(Not available)' then 'SMB'
when dr.chain <> '(Not available)' then 'SA'
end as sales_pillar,
dr.partner_type as partner_type,
case
when coalesce(opi.orders_3month, 0) = 0 then '0 orders'
when coalesce(opi.orders_3month, 0) between 1 and 100 then '1-100 orders'
when coalesce(opi.orders_3month, 0) between 101 and 500 then '101-500 orders'
when coalesce(opi.orders_3month, 0) > 500 then '500+ orders'
else 'Error'
end as order_bucket,
-- Churned & Reactivations
count(distinct (case
when cndm.churn_no_oc_ind = 1 then cndm.restaurantid
else null end)) as num_churns
from `just-data-warehouse.sales_analytics_datamarts.churn_new_def_mart` as cndm
inner join `just-data-warehouse.dwh.dim_restaurant` as dr
on cast(cndm.restaurantid as string) = cast(dr.restaurantid as string)
left join orders_partner_id as opi on opi.restaurant_id = cndm.restaurantid
and opi.month_dt = date_trunc(cast(cndm.status_change_date as date), month)
where cast(cndm.status_change_date as date) >= '2022-01-01'
and cast(cndm.status_change_date as date) < date_trunc(cast(current_date as date), month)
and dr.deliverytype <> 'Vending'
and dr.testrestaurant <> 'Is Test Restaurant'
and dr.partner_type in ('Restaurant', 'Grocery')
group by 1, 2, 3, 4, 5, 6
order by 1, 2, 3, 4, 5, 6),
achieved_op as (
select
date_trunc(cast(frds.snapshot_dt as date), month) as month_dt,
case
when dr.country in ('AU', 'NZ') then 'ANZ'
when dr.country in ('BE', 'LU') then 'BEL'
else dr.country
end as country,
case
when dr.country in ('AT', 'BE', 'LU', 'CH', 'DE', 'DK', 'NL', 'PL', 'SK')
then 'NE'
when dr.country in ('GB', 'IE')
then 'UKI'
when dr.country in ('AU', 'NZ', 'BG', 'ES', 'FR', 'IL', 'IT')
then 'SEANZ'
else 'Other' end as segment,
case
when dr.chain = '(Not available)' then 'SMB'
when dr.chain <> '(Not available)' then 'SA'
end as sales_pillar,
dr.partner_type as partner_type,
case
when coalesce(opi.orders_3month, 0) = 0 then '0 orders'
when coalesce(opi.orders_3month, 0) between 1 and 100 then '1-100 orders'
when coalesce(opi.orders_3month, 0) between 101 and 500 then '101-500 orders'
when coalesce(opi.orders_3month, 0) > 500 then '500+ orders'
else 'Error'
end as order_bucket,
-- Active Partners
count(distinct (case when ((frds.end_of_month_flag = 1) and (frds.online_status_id in (4, 6)))
then frds.restaurantid
else null end)) as online_partners
from `just-data-warehouse.dwh.fact_restaurant_daily_snapshot` as frds
inner join `just-data-warehouse.dwh.dim_restaurant` as dr
on cast(frds.restaurantid as string) = cast(dr.restaurantid as string)
left join orders_partner_id as opi on opi.restaurant_id = frds.restaurantid
and opi.month_dt = date_trunc(cast(frds.snapshot_dt as date), month)
where
cast(frds.snapshot_dt as date) >= '2022-01-01'
and cast(frds.snapshot_dt as date) < date_trunc(cast(current_date as date), month)
and dr.deliverytype <> 'Vending'
and dr.testrestaurant <> 'Is Test Restaurant'
and dr.partner_type in ('Restaurant', 'Grocery')
group by 1, 2, 3, 4, 5, 6
order by 1, 2, 3, 4, 5, 6)
select
ae.month_dt as month_dt,
ae.country as country,
ae.segment as segment,
ae.sales_pillar as sales_pillar,
ae.partner_type as partner_type,
ae.order_bucket as order_bucket,
ae.num_churns as num_churns,
ao.online_partners as online_partners,
from achieved_churn as ae
left join achieved_op as ao
on ae.month_dt = ao.month_dt and
ae.country = ao.country and
ae.segment = ao.segment and
ae.sales_pillar = ao.sales_pillar and
ae.partner_type = ao.partner_type and
ae.order_bucket = ao.order_bucket)
select t2.month_dt as month_df,
t2.country as country,
t2.segment as segment,
t2.sales_pillar as sales_pillar,
t2.partner_type as partner_type,
t2.order_bucket as order_bucket,
metric.metric_name as metric_name,
'Achieved' as value_type,
metric.value as value
from (
select
t.month_dt,
t.country,
t.segment,
t.sales_pillar,
t.partner_type,
t.order_bucket,
[struct ('Online Partners' as metric_name, t.online_partners as value),
struct ('Churns' as metric_name, coalesce(t.num_churns, 0) as value)] as metrics,
from t) as t2
cross join unnest(t2.metrics) as metric);
select *
from target_values
union all
(select * from achieved_values);
end;
Leave a Comment