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