Skip to content

Instantly share code, notes, and snippets.

@dltm-markwan

dltm-markwan/orders_by_nation_storedProc.sql

Created October 24, 2023 00:28
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save dltm-markwan/2f6c211910606755d1beac8d7941c197 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save dltm-markwan/2f6c211910606755d1beac8d7941c197 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
orders_by_nation_storedProc.sql
CREATE OR REPLACE PROCEDURE ANALYTICS.dbt_markwan_sprocs.CalculateMonthlySalesByNation()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
-- Create the temporary table for months
CREATE OR REPLACE TEMPORARY TABLE temp_months AS (
WITH RECURSIVE cte_months AS (
SELECT TO_DATE('1995-01-01') AS month
UNION ALL
SELECT DATEADD('MONTH', 1, MONTH)
FROM cte_months
WHERE DATEADD('MONTH', 1, MONTH) <= TO_DATE('1998-01-01')
)
SELECT month FROM cte_months
);
-- Create the temporary table for sales data
CREATE OR REPLACE TEMPORARY TABLE temp_sales AS (
SELECT
MD5(
CAST(
COALESCE(
CAST(temp_months.month AS TEXT)
, '_dbt_utils_surrogate_key_null_'
)
|| '-'
|| COALESCE(
CAST(nation AS TEXT), '_dbt_utils_surrogate_key_null_'
)
|| '-'
|| COALESCE(
CAST(region AS TEXT), '_dbt_utils_surrogate_key_null_'
)
|| '-'
|| COALESCE(
CAST(market_segment AS TEXT)
, '_dbt_utils_surrogate_key_null_'
) AS TEXT
)
) AS surrogate_key_hash
, temp_months.month AS order_month
, dim_customers.nation
, dim_customers.region
, dim_customers.market_segment
, SUM(fct_orders.gross_item_sales_amount) AS gross_item_sales_amount
FROM
temp_months
CROSS JOIN
analytics.dbt_markwan.dim_customers
LEFT JOIN
analytics.dbt_markwan.fct_orders
ON DATE_TRUNC('MONTH', fct_orders.order_date) = temp_months.month
AND fct_orders.customer_key = dim_customers.customer_key
-- WHERE
-- dim_customers.region = 'ASIA' -- Filter by the provided region
GROUP BY
order_month
, dim_customers.nation
, dim_customers.region
, dim_customers.market_segment
);
-- Create the monthly_sales_by_nation table if it doesn't exist
CREATE TABLE IF NOT EXISTS monthly_sales_by_nation (
surrogate_key_hash VARCHAR
, order_month DATE
, nation VARCHAR
, region VARCHAR
, market_segment VARCHAR
, gross_item_sales_amount DECIMAL(18, 2)
, start_date DATE
, end_date DATE
, CONSTRAINT unique_key PRIMARY KEY (surrogate_key_hash)
);
-- Create the monthly_sales_by_nation_scd2 table if it doesn't exist
CREATE TABLE IF NOT EXISTS monthly_sales_by_nation_scd2 (
surrogate_key_hash VARCHAR
, order_month DATE
, nation VARCHAR
, region VARCHAR
, market_segment VARCHAR
, gross_item_sales_amount DECIMAL(18, 2)
, start_date DATE
, end_date DATE
, CONSTRAINT unique_key PRIMARY KEY (surrogate_key_hash, start_date)
);
-- Insert new data into the monthly_sales_by_nation table
INSERT INTO monthly_sales_by_nation (
surrogate_key_hash
, order_month
, nation
, region
, market_segment
, gross_item_sales_amount
, start_date
, end_date
)
SELECT
src.surrogate_key_hash
, src.order_month
, src.nation
, src.region
, src.market_segment
, src.gross_item_sales_amount
, src.order_month
, NULL
FROM
temp_sales AS src
WHERE
NOT EXISTS (
SELECT 1
FROM monthly_sales_by_nation AS tgt
WHERE
tgt.surrogate_key_hash = src.surrogate_key_hash
AND tgt.order_month = src.order_month
);
-- Update end dates for existing records and insert new SCD Type 2 changes
MERGE INTO monthly_sales_by_nation AS tgt
USING (
SELECT
src.surrogate_key_hash
, src.order_month
, src.nation
, src.region
, src.market_segment
, src.gross_item_sales_amount
, src.order_month AS start_date
, NULL AS end_date
FROM
temp_sales AS src
WHERE
EXISTS (
SELECT 1
FROM monthly_sales_by_nation AS m
WHERE
m.surrogate_key_hash = src.surrogate_key_hash
AND m.order_month = src.order_month
AND (m.nation <> src.nation OR m.region <> src.region OR m.market_segment <> src.market_segment OR m.gross_item_sales_amount <> src.gross_item_sales_amount)
)
) AS src
ON
tgt.surrogate_key_hash = src.surrogate_key_hash
AND tgt.end_date IS NULL
WHEN MATCHED THEN
UPDATE SET
tgt.end_date = src.start_date - INTERVAL '1 DAY'
WHEN NOT MATCHED THEN
INSERT (surrogate_key_hash, order_month, nation, region, market_segment, gross_item_sales_amount, start_date, end_date)
VALUES (src.surrogate_key_hash, src.order_month, src.nation, src.region, src.market_segment, src.gross_item_sales_amount, src.start_date, src.end_date);
-- Create the active_orders table if it doesn't exist
CREATE TABLE IF NOT EXISTS active_orders (
surrogate_key_hash VARCHAR
, order_month DATE
, nation VARCHAR
, region VARCHAR
, market_segment VARCHAR
, gross_item_sales_amount DECIMAL(18, 2)
, start_date DATE
, end_date DATE
, CONSTRAINT unique_key PRIMARY KEY (surrogate_key_hash)
);
-- Insert active records into the active_orders table
INSERT INTO active_orders
SELECT *
FROM monthly_sales_by_nation
WHERE end_date IS NULL;
-- Cleanup: Drop the temporary tables
DROP TABLE temp_months;
DROP TABLE temp_sales;
RETURN 'Stored procedure executed successfully.';
END;
$$;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment