fredpinchon

fredpinchon / How to build a history table.sql

Created March 25, 2021 00:26

Code for blog post How to build a history table with Snowflake

	-- Initialize OPPORTUNITIES table in Production
	CREATE OR REPLACE TABLE "LAKE_OF_DATA"."PRODUCTION"."OPPORTUNITIES" CLONE "LAKE_OF_DATA"."STAGING"."OPPORTUNITIES";

	-- Initialize OPPORTUNITIES_HISTORY table
	CREATE OR REPLACE TABLE "LAKE_OF_DATA"."PRODUCTION"."OPPORTUNITIES_HISTORY" AS
	SELECT CURRENT_DATE as DATE_FROM,NULL::DATE as DATE_TO,1::BOOLEAN as IS_ACTIVE,OPPID::TEXT as OPPID, OPP_NAME,CLOSE_DATE::DATE as CLOSE_DATE,OWNER,ACCOUNT,AMOUNT
	from "LAKE_OF_DATA"."STAGING"."OPPORTUNITIES";

	--Create Stream to track changes
	create or replace stream opportunity_stream on table "LAKE_OF_DATA"."PRODUCTION"."OPPORTUNITIES";

fredpinchon / pivot-unpivot

Last active October 13, 2021 22:21

Snowflake Scripts to illustrate Pivot/Unpivot blog post

	--- Create Table
	create or replace table Orders_Demo (
	RecordId varchar(2) ,
	OrderId varchar(255) ,
	Order_Date date ,
	CustomerId varchar(20) ,
	Customer_Name varchar(80) ,
	Segment varchar(20) ,
	ProductId varchar(40) ,
	Category varchar(20),

fredpinchon / Create Order Counts Blog post.sql

Last active August 25, 2020 05:32

Snowflake Code for blog Post: Order Counts

	// How to create rows to count properly line items
	// Customer Cohorts
	// Step 0: Show Orders details by Customers
	select "Customer Name","Order_Date","Order ID","Product Name","Quantity"
	from "INTERNAL"."PUBLIC"."SuperStore"
	order by 1,2;

	// Step 1: Show multiple Orders by Customers
	select "Customer Name","Order_Date","Order ID", count(distinct "Order ID") as Orders,SUM(1) as "Line Items"
	from "INTERNAL"."PUBLIC"."SuperStore"