View: Intermediary DRP Table(s): drp.sku_launch_date

Source Database Table

drp.sku_launch_date

Script that Populates Database Table

6520_PLN_BAS_sku_launch_date.sql

Script Description and Logic

Pulls the first date a sku was purchased or launched at the product and store level. Products from all integrations are included in the primary source table, drp.order_line_revenue, and reported by unique sku launch id and shop. From the drp.order_line_revenue table, a join is made to drp.order_business unit variants to segment skus by unique stores and the earliest order date. The resulting table is then staged for insertion. Incremental functionality is included such that only the records that require an update or are new get inserted into the final drp.sku_launch_date table. The records in staging are compared to those in the final table. Any sku launch ids that match are removed from the final table and updated by way of insertion from staging (upsert). The records that appear in staging, but not in the final table are inserted into drp.sku_launch_date.

Source Tables Used In Script

Schema Table (or Derived Table) Name Table Type Purpose
drp order_line_revenue database Primary
drp order_business_unit database Lookup
drp_staging sku_launch_date database Pre-insert

SQL Flow

Calculated and Derived Fields

Target Column Target Column Data Type Source schema.table Source Column Transformation/Logic
sku_launch_id CHAR drp.order_line_revenue, drp.order_business_unit store_name, sku store_name + sku
sku_launch_date TIMESTAMP WITHOUT TIME ZONE drp.order_line_revenue order_date MIN(order_date) of a product by unique store and sku

 

All content © Daasity 2021. Do not copy, share or distribute.