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.