API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Nov 24th, 2016
293
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PostgreSQL 12.05 KB | None | 0 0
raw download clone embed print report
  1.  
  2. -- calculate the rolling absolute metrics (not ratios) at each customer age
  3.  
  4.     DROP TABLE IF EXISTS level24_facts_customer_state.rolling_absolute_metrics_lifetime_cumulative;
  5.     SELECT
  6.         -- ## level24_facts_customer_state.customer_timeline_weekly_extended
  7.             a.customer_id,
  8.             a.customer_country,
  9.             a.is_main_market_customer,
  10.             a.date_week,
  11.             a.life_week_idx,
  12.             a.has_returns_deadline_passed_week,
  13.             a.has_cancellations_deadline_passed_week,
  14.         -- CUMULATIVE
  15.             a.life_week_idx::numeric AS cumulative_life_weeks, -- total weeks in lifecycle up until that point
  16.             CASE WHEN a.life_week_idx >= 14 THEN ROUND(a.life_week_idx::numeric / 13, 2) END AS cumulative_life_quarters,
  17.             CASE WHEN a.life_week_idx >= 53 THEN ROUND(a.life_week_idx::numeric / 52, 2) END AS cumulative_life_years,
  18.             SUM(SUM(CASE WHEN a.order_date_week IS NOT NULL THEN 1 END)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC) AS cumulative_weeks_active, -- total weeks active up until that point
  19.         -- LIFETIME CUMULATIVE
  20.                 a.prior_cumulative_order_count,
  21.             SUM(SUM(a.order_count)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_order_count,
  22.             SUM(SUM(a.order_count_complete)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_order_count_complete,
  23.             SUM(SUM(a.units_ordered_count_complete)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_units_ordered_count_complete,
  24.             SUM(SUM(a.units_delivered_count_complete)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_units_delivered_count_complete,
  25.             SUM(SUM(a.units_returned_count_complete)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_units_returned_count_complete,
  26.             SUM(SUM(a.units_cancelled_count_complete)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_units_cancelled_count_complete,
  27.             SUM(SUM(a.order_count_with_markdown_30plus)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_order_count_with_markdown_30plus,
  28.             SUM(SUM(a.order_count_with_promo_voucher_10plus)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_order_count_with_promo_voucher_10plus,
  29.             SUM(SUM(a.order_count_with_good_discount)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_order_count_with_good_discount,
  30.                 SUM(SUM(a.units_ordered_count)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_units_ordered_count,
  31.                 SUM(SUM(a.units_ordered_count_dresses)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_units_ordered_count_dresses,
  32.                 SUM(SUM(a.units_ordered_count_blouses)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_units_ordered_count_blouses,
  33.                 SUM(SUM(a.units_ordered_count_tops)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_units_ordered_count_tops,
  34.                 SUM(SUM(a.units_ordered_count_jackets)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_units_ordered_count_jackets,
  35.                 SUM(SUM(a.units_ordered_count_knitwear)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_units_ordered_count_knitwear,
  36.                 SUM(SUM(a.units_ordered_count_trousers)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_units_ordered_count_trousers,
  37.                 SUM(SUM(a.units_ordered_count_skirts)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_units_ordered_count_skirts,
  38.                 SUM(SUM(a.units_ordered_count_pl1_offered_selling_price)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_units_ordered_count_pl1_offered_selling_price,
  39.                 SUM(SUM(a.units_ordered_count_pl2_offered_selling_price)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_units_ordered_count_pl2_offered_selling_price,
  40.                 SUM(SUM(a.units_ordered_count_pl3_offered_selling_price)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_units_ordered_count_pl3_offered_selling_price,
  41.                 SUM(SUM(a.units_ordered_count_pl4_offered_selling_price)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_units_ordered_count_pl4_offered_selling_price,
  42.                 SUM(SUM(a.units_ordered_count_pl5_offered_selling_price)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_units_ordered_count_pl5_offered_selling_price,
  43.                 SUM(SUM(a.units_cancelled_count)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_units_cancelled_count,
  44.                 SUM(SUM(a.units_returned_count)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_units_returned_count,
  45.                 SUM(SUM(a.units_delivered_count)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_units_delivered_count,
  46.                 SUM(SUM(a.units_kept_count)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_units_kept_count,
  47.             SUM(SUM(a.value_product_cost_goods_kept)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_product_cost_goods_kept,
  48.             SUM(SUM(a.value_product_cost_goods_ordered)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_product_cost_goods_ordered,
  49.             SUM(SUM(a.value_product_net_margin)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_product_net_margin,
  50.             SUM(SUM(a.value_product_gross_margin)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_product_gross_margin,
  51.             SUM(SUM(a.value_product_sales_excl_vat)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_product_sales_excl_vat,
  52.             SUM(SUM(a.value_product_gross_sales)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_product_gross_sales,
  53.             SUM(SUM(a.value_product_sales_full_price_excl_vat)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_product_sales_full_price_excl_vat,
  54.             SUM(SUM(a.value_product_effective_promotion_voucher_excl_vat)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_product_effective_promotion_voucher_excl_vat,
  55.             SUM(SUM(a.value_product_gross_sales_excl_shipping)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_product_gross_sales_excl_shipping,
  56.             SUM(SUM(a.value_product_gross_sales_pos_excl_vat)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_product_gross_sales_pos_excl_vat,
  57.             SUM(SUM(a.value_product_gross_sales_pos_incl_vat)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_product_gross_sales_pos_incl_vat,
  58.             SUM(SUM(a.value_product_markdown_excl_vat)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_product_markdown_excl_vat,
  59.             SUM(SUM(a.value_product_markdown_incl_vat)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_product_markdown_incl_vat,
  60.             SUM(SUM(a.value_product_net_sales)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_product_net_sales,
  61.             MAX(MAX(a.value_max_product_gross_sales_pos_excl_vat)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_max_product_gross_sales_pos_excl_vat,
  62.             AVG(AVG(a.value_avg_product_gross_sales_pos_excl_vat)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_avg_product_gross_sales_pos_excl_vat,
  63.             AVG(AVG(a.value_avg_product_sales_full_price_excl_vat)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_avg_product_sales_full_price_excl_vat,
  64.             AVG(AVG(a.value_avg_product_selling_price_excl_vat)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_avg_product_selling_price_excl_vat,
  65.             AVG(AVG(a.value_avg_product_selling_price_incl_vat)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_avg_product_selling_price_incl_vat,
  66.             SUM(SUM(a.value_product_total_cancellations_excl_vat)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_product_total_cancellations_excl_vat,
  67.             SUM(SUM(a.value_product_total_credit_incl_vat)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_product_total_credit_incl_vat,
  68.             SUM(SUM(a.value_product_total_returns_excl_vat)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_product_total_returns_excl_vat,
  69.             SUM(SUM(a.value_product_other_direct_cost_without_shipping_excl_vat)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_product_other_direct_cost_without_shipping_excl_vat,
  70.             SUM(SUM(a.value_product_cost_delivery_sent_excl_vat)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_product_cost_delivery_sent_excl_vat,
  71.             SUM(SUM(a.value_product_cost_delivery_returned_excl_vat)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_product_cost_delivery_returned_excl_vat,
  72.             SUM(SUM(a.value_product_cpo_excl_vat)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_product_cpo_excl_vat,
  73.             SUM(SUM(a.value_product_cpnco_max_cpa_excl_vat)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_product_cpnco_max_cpa_excl_vat,
  74.             SUM(SUM(a.value_product_cpnco_acquisition_potential_excl_vat)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_product_cpnco_acquisition_potential_excl_vat,
  75.             SUM(SUM(a.value_product_cprco_retention_potential_excl_vat)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_product_cprco_retention_potential_excl_vat,
  76.             SUM(SUM(a.value_product_cm1)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_product_cm1,
  77.             SUM(SUM(a.value_product_cm2_cpo_model)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_product_cm2_cpo_model,
  78.             SUM(SUM(a.value_product_cm2_max_cpa_model)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_product_cm2_max_cpa_model,
  79.             SUM(SUM(a.value_product_cm2_cpo_acqret_model)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_product_cm2_cpo_acqret_model,
  80.             -- ESTIMATES
  81.                 SUM(SUM(a.value_actuals_estimated_product_net_sales)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_estimated_product_net_sales,
  82.                 SUM(SUM(a.value_actuals_estimated_product_cm1)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_estimated_product_cm1,
  83.                 SUM(SUM(a.value_actuals_estimated_product_cm2_cpo_model)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_estimated_product_cm2_cpo_model,
  84.                 SUM(SUM(a.value_actuals_estimated_product_cm2_max_cpa_model)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_estimated_product_cm2_max_cpa_model,
  85.                 SUM(SUM(a.value_actuals_estimated_product_cm2_cpo_acqret_model)) OVER (PARTITION BY a.customer_id ORDER BY a.date_week ASC)::numeric AS cumulative_value_estimated_product_cm2_cpo_acqret_model
  86.     INTO
  87.         level24_facts_customer_state.rolling_absolute_metrics_lifetime_cumulative
  88.     FROM
  89.         level24_facts_customer_state.customer_timeline_weekly_extended AS a
  90.     GROUP BY
  91.         a.customer_id,
  92.         a.date_week
  93.     ;
  94.  
  95.  
  96.  
  97. ALTER TABLE level24_facts_customer_state.rolling_absolute_metrics_lifetime_cumulative ADD PRIMARY KEY (customer_id, date_week);
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!