Chance Insight – Analytics Blog (AI Enhanced)

Category: SQL

  • SQL Code comparisons: SQL Server, Snowflake, BigQuery

    When jumping from one project to another, it can be useful to be able to compare common code structures.
    I couldn’t find anything like this out there, so here it is magically created.

    Contents:
    Common Code Structures
    Working with Dates
    Window Functions
    Error Handling
    Casting
    Joining Tables
    CTE (Common Table Expressions)

    Common Code Structures

    Topic SQL Server Snowflake BigQuery
    Select & Filtering 
    SELECT TOP 10 *
FROM Sales
WHERE Amount > 1000;
    		 
    SELECT *
FROM Sales
WHERE Amount > 1000
LIMIT 10;
    		 
    SELECT *
FROM `project.dataset.Sales`
WHERE Amount > 1000
LIMIT 10;
    String Functions 
    SELECT LEFT(CustomerName, 5), LEN(CustomerName)
FROM Customers;
    		 
    SELECT LEFT(CustomerName, 5), LENGTH(CustomerName)
FROM Customers;
    		 
    SELECT SUBSTR(CustomerName, 1, 5), LENGTH(CustomerName)
FROM `project.dataset.Customers`;
    Date Functions 
    SELECT GETDATE() AS CurrentDate, DATEADD(DAY, 7, GETDATE());
    		 
    SELECT CURRENT_DATE, DATEADD(DAY, 7, CURRENT_DATE);
    		 
    SELECT CURRENT_DATE(), DATE_ADD(CURRENT_DATE(), INTERVAL 7 DAY);
    Joins 
    SELECT c.CustomerName, o.OrderID
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID;
    		 
    SELECT c.CustomerName, o.OrderID
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID;
    		 
    SELECT c.CustomerName, o.OrderID
FROM `project.dataset.Customers` c
JOIN `project.dataset.Orders` o
ON c.CustomerID = o.CustomerID;
    Aggregations & Group By 
    SELECT CustomerID, SUM(Amount) AS TotalAmount
FROM Sales
GROUP BY CustomerID
HAVING SUM(Amount) > 1000;
    		 
    SELECT CustomerID, SUM(Amount) AS TotalAmount
FROM Sales
GROUP BY CustomerID
HAVING SUM(Amount) > 1000;
    		 
    SELECT CustomerID, SUM(Amount) AS TotalAmount
FROM `project.dataset.Sales`
GROUP BY CustomerID
HAVING SUM(Amount) > 1000;
    Window Functions 
    SELECT CustomerID,
       SUM(Amount) OVER (PARTITION BY CustomerID) AS TotalByCustomer
FROM Sales;
    		 
    SELECT CustomerID,
       SUM(Amount) OVER (PARTITION BY CustomerID) AS TotalByCustomer
FROM Sales;
    		 
    SELECT CustomerID,
       SUM(Amount) OVER (PARTITION BY CustomerID) AS TotalByCustomer
FROM `project.dataset.Sales`;
    Creating Tables 
    CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName NVARCHAR(100)
);
    		 
    CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName STRING
);
    		 
    CREATE TABLE dataset.Customers (
    CustomerID INT64,
    CustomerName STRING
);
    Insert 
    INSERT INTO Customers (CustomerID, CustomerName)
VALUES (1, 'John Doe');
    		 
    INSERT INTO Customers (CustomerID, CustomerName)
VALUES (1, 'John Doe');
    		 
    INSERT INTO dataset.Customers (CustomerID, CustomerName)
VALUES (1, 'John Doe');
    Update 
    UPDATE Customers
SET CustomerName = 'Jane Doe'
WHERE CustomerID = 1;
    		 
    UPDATE Customers
SET CustomerName = 'Jane Doe'
WHERE CustomerID = 1;
    		 
    UPDATE dataset.Customers
SET CustomerName = 'Jane Doe'
WHERE CustomerID = 1;
    Delete 
    DELETE FROM Customers WHERE CustomerID = 1;
    		 
    DELETE FROM Customers WHERE CustomerID = 1;
    		 
    DELETE FROM dataset.Customers WHERE CustomerID = 1;
    Case When Statements 
    SELECT OrderID,
       CASE 
         WHEN Amount >= 1000 THEN 'High'
         WHEN Amount >= 500  THEN 'Medium'
         ELSE 'Low'
       END AS OrderCategory
FROM Sales;
    		 
    SELECT OrderID,
       CASE 
         WHEN Amount >= 1000 THEN 'High'
         WHEN Amount >= 500  THEN 'Medium'
         ELSE 'Low'
       END AS OrderCategory
FROM Sales;
    		 
    SELECT OrderID,
       CASE 
         WHEN Amount >= 1000 THEN 'High'
         WHEN Amount >= 500  THEN 'Medium'
         ELSE 'Low'
       END AS OrderCategory
FROM `project.dataset.Sales`;
    Start of Month 
    SELECT OrderDate,
       DATEADD(MONTH, DATEDIFF(MONTH, 0, OrderDate), 0) AS StartOfMonth
FROM Orders;
    		 
    SELECT OrderDate,
       DATE_TRUNC('MONTH', OrderDate) AS StartOfMonth
FROM Orders;
    		 
    SELECT OrderDate,
       DATE_TRUNC(OrderDate, MONTH) AS StartOfMonth
FROM `project.dataset.Orders`;
    End of Month 
    SELECT OrderDate,
       EOMONTH(OrderDate) AS EndOfMonth
FROM Orders;
    		 
    SELECT OrderDate,
       LAST_DAY(OrderDate, 'MONTH') AS EndOfMonth
FROM Orders;
    		 
    SELECT OrderDate,
       LAST_DAY(OrderDate, MONTH) AS EndOfMonth
FROM `project.dataset.Orders`;
    Null Handling 
    SELECT CustomerID,
       COALESCE(CustomerName, 'Unknown') AS SafeName
FROM Customers;
    		 
    SELECT CustomerID,
       COALESCE(CustomerName, 'Unknown') AS SafeName
FROM Customers;
    		 
    SELECT CustomerID,
       COALESCE(CustomerName, 'Unknown') AS SafeName
FROM `project.dataset.Customers`;
    Conditional Aggregation 
    SELECT CustomerID,
       SUM(CASE WHEN Amount > 1000 THEN Amount ELSE 0 END) AS HighValueSales
FROM Sales
GROUP BY CustomerID;
    		 
    SELECT CustomerID,
       SUM(CASE WHEN Amount > 1000 THEN Amount ELSE 0 END) AS HighValueSales
FROM Sales
GROUP BY CustomerID;
    		 
    SELECT CustomerID,
       SUM(CASE WHEN Amount > 1000 THEN Amount ELSE 0 END) AS HighValueSales
FROM `project.dataset.Sales`
GROUP BY CustomerID;
    Date Difference 
    SELECT OrderID,
       DATEDIFF(DAY, OrderDate, ShippedDate) AS DaysToShip
FROM Orders;
    		 
    SELECT OrderID,
       DATEDIFF(DAY, OrderDate, ShippedDate) AS DaysToShip
FROM Orders;
    		 
    SELECT OrderID,
       DATE_DIFF(ShippedDate, OrderDate, DAY) AS DaysToShip
FROM `project.dataset.Orders`;

    Working with Dates

    Date Topic SQL Server Snowflake BigQuery
    Build Date from Parts 
    SELECT DATEFROMPARTS(2025, 10, 3) AS d;
    		 
    SELECT DATE_FROM_PARTS(2025, 10, 3) AS d;
    		 
    SELECT DATE(2025, 10, 3) AS d;
    Parse Date from String 
    SELECT TRY_CONVERT(date, '03/10/2025', 103); -- DD/MM/YYYY
SELECT CONVERT(date, '2025-10-03', 23);       -- ISO
    		 
    SELECT TO_DATE('03/10/2025', 'DD/MM/YYYY');
SELECT TO_DATE('2025-10-03');
    		 
    SELECT PARSE_DATE('%d/%m/%Y','03/10/2025');
SELECT DATE '2025-10-03';
    Current Date / Timestamp 
    SELECT CAST(GETDATE() AS date) AS current_date,
       GETDATE() AS current_datetime;
    		 
    SELECT CURRENT_DATE AS current_date,
       CURRENT_TIMESTAMP AS current_ts;
    		 
    SELECT CURRENT_DATE() AS current_date,
       CURRENT_TIMESTAMP() AS current_ts;
    Start of Month 
    SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, OrderDate), 0)
FROM Orders;
    		 
    SELECT DATE_TRUNC('MONTH', OrderDate)
FROM Orders;
    		 
    SELECT DATE_TRUNC(OrderDate, MONTH)
FROM `project.dataset.Orders`;
    End of Month 
    SELECT EOMONTH(OrderDate)
FROM Orders;
    		 
    SELECT LAST_DAY(OrderDate, 'MONTH')
FROM Orders;
    		 
    SELECT LAST_DAY(OrderDate, MONTH)
FROM `project.dataset.Orders`;
    Add Days 
    SELECT DATEADD(DAY, 7, OrderDate)
FROM Orders;
    		 
    SELECT DATEADD(DAY, 7, OrderDate)
FROM Orders;
    		 
    SELECT DATE_ADD(OrderDate, INTERVAL 7 DAY)
FROM `project.dataset.Orders`;
    Add Months 
    SELECT DATEADD(MONTH, 3, OrderDate)
FROM Orders;
    		 
    SELECT DATEADD(MONTH, 3, OrderDate)
FROM Orders;
    		 
    SELECT DATE_ADD(OrderDate, INTERVAL 3 MONTH)
FROM `project.dataset.Orders`;
    Difference (Days) 
    SELECT DATEDIFF(DAY, OrderDate, ShippedDate)
FROM Orders;
    		 
    SELECT DATEDIFF('DAY', OrderDate, ShippedDate)
FROM Orders;
    		 
    SELECT DATE_DIFF(ShippedDate, OrderDate, DAY)
FROM `project.dataset.Orders`;
    Difference (Months) 
    SELECT DATEDIFF(MONTH, StartDate, EndDate) AS months_between;
    		 
    SELECT DATEDIFF('MONTH', StartDate, EndDate) AS months_between;
    		 
    SELECT DATE_DIFF(EndDate, StartDate, MONTH) AS months_between;
    Truncate to Week (Week Start) 
    -- Week starting Sunday
SELECT DATEADD(WEEK, DATEDIFF(WEEK, 0, OrderDate), 0)
FROM Orders;
    		 
    -- ISO week (Mon start)
SELECT DATE_TRUNC('WEEK', OrderDate)
FROM Orders;
    		 
    -- Default WEEK (Sun). Use WEEK(MONDAY) if needed
SELECT DATE_TRUNC(OrderDate, WEEK)
FROM `project.dataset.Orders`;
    Truncate to Quarter 
    SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, OrderDate), 0)
FROM Orders;
    		 
    SELECT DATE_TRUNC('QUARTER', OrderDate)
FROM Orders;
    		 
    SELECT DATE_TRUNC(OrderDate, QUARTER)
FROM `project.dataset.Orders`;
    First Day of Year 
    SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, OrderDate), 0)
FROM Orders;
    		 
    SELECT DATE_TRUNC('YEAR', OrderDate)
FROM Orders;
    		 
    SELECT DATE_TRUNC(OrderDate, YEAR)
FROM `project.dataset.Orders`;
    Last Day of Year 
    SELECT EOMONTH(DATEFROMPARTS(YEAR(OrderDate), 12, 1))
FROM Orders;
    		 
    SELECT LAST_DAY(OrderDate, 'YEAR')
FROM Orders;
    		 
    SELECT LAST_DAY(OrderDate, YEAR)
FROM `project.dataset.Orders`;
    Extract Year / Month / Day 
    SELECT YEAR(OrderDate) AS y,
       MONTH(OrderDate) AS m,
       DAY(OrderDate) AS d
FROM Orders;
    		 
    SELECT YEAR(OrderDate) AS y,
       MONTH(OrderDate) AS m,
       DAY(OrderDate) AS d
FROM Orders;
    		 
    SELECT EXTRACT(YEAR FROM OrderDate) AS y,
       EXTRACT(MONTH FROM OrderDate) AS m,
       EXTRACT(DAY FROM OrderDate) AS d
FROM `project.dataset.Orders`;
    Day Name / Weekday 
    SELECT DATENAME(WEEKDAY, OrderDate) AS day_name,
       DATEPART(WEEKDAY, OrderDate) AS weekday_num
FROM Orders;
    		 
    SELECT DAYNAME(OrderDate) AS day_name,
       DAYOFWEEK(OrderDate) AS weekday_num
FROM Orders;
    		 
    SELECT FORMAT_DATE('%A', OrderDate) AS day_name,
       EXTRACT(DAYOFWEEK FROM OrderDate) AS weekday_num
FROM `project.dataset.Orders`;
    Between Dates (Inclusive) 
    SELECT *
FROM Orders
WHERE OrderDate BETWEEN '2025-10-01' AND '2025-10-31';
    		 
    SELECT *
FROM Orders
WHERE OrderDate BETWEEN '2025-10-01' AND '2025-10-31';
    		 
    SELECT *
FROM `project.dataset.Orders`
WHERE OrderDate BETWEEN DATE '2025-10-01' AND DATE '2025-10-31';
    Last 30 Days Filter 
    SELECT *
FROM Orders
WHERE OrderDate >= DATEADD(DAY, -30, CAST(GETDATE() AS date));
    		 
    SELECT *
FROM Orders
WHERE OrderDate >= DATEADD(DAY, -30, CURRENT_DATE);
    		 
    SELECT *
FROM `project.dataset.Orders`
WHERE OrderDate >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY);

    Window Functions

    Window Topic SQL Server Snowflake BigQuery
    Running Total 
    SELECT CustomerID, OrderDate, Amount,
  SUM(Amount) OVER (
    PARTITION BY CustomerID
    ORDER BY OrderDate
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS RunningTotal
FROM Sales;
    		 
    SELECT CustomerID, OrderDate, Amount,
  SUM(Amount) OVER (
    PARTITION BY CustomerID
    ORDER BY OrderDate
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS RunningTotal
FROM Sales;
    		 
    SELECT CustomerID, OrderDate, Amount,
  SUM(Amount) OVER (
    PARTITION BY CustomerID
    ORDER BY OrderDate
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS RunningTotal
FROM `project.dataset.Sales`;
    Moving Avg (7 rows) 
    SELECT OrderDate, Amount,
  AVG(Amount) OVER (
    ORDER BY OrderDate
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS MovAvg7
FROM Sales;
    		 
    SELECT OrderDate, Amount,
  AVG(Amount) OVER (
    ORDER BY OrderDate
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS MovAvg7
FROM Sales;
    		 
    SELECT OrderDate, Amount,
  AVG(Amount) OVER (
    ORDER BY OrderDate
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS MovAvg7
FROM `project.dataset.Sales`;
    ROW_NUMBER 
    SELECT *,
  ROW_NUMBER() OVER (
    PARTITION BY CustomerID ORDER BY OrderDate DESC
  ) AS rn
FROM Sales;
    		 
    SELECT *,
  ROW_NUMBER() OVER (
    PARTITION BY CustomerID ORDER BY OrderDate DESC
  ) AS rn
FROM Sales;
    		 
    SELECT *,
  ROW_NUMBER() OVER (
    PARTITION BY CustomerID ORDER BY OrderDate DESC
  ) AS rn
FROM `project.dataset.Sales`;
    RANK 
    SELECT CustomerID, Amount,
  RANK() OVER (PARTITION BY CustomerID ORDER BY Amount DESC) AS rnk
FROM Sales;
    		 
    SELECT CustomerID, Amount,
  RANK() OVER (PARTITION BY CustomerID ORDER BY Amount DESC) AS rnk
FROM Sales;
    		 
    SELECT CustomerID, Amount,
  RANK() OVER (PARTITION BY CustomerID ORDER BY Amount DESC) AS rnk
FROM `project.dataset.Sales`;
    DENSE_RANK 
    SELECT CustomerID, Amount,
  DENSE_RANK() OVER (PARTITION BY CustomerID ORDER BY Amount DESC) AS drnk
FROM Sales;
    		 
    SELECT CustomerID, Amount,
  DENSE_RANK() OVER (PARTITION BY CustomerID ORDER BY Amount DESC) AS drnk
FROM Sales;
    		 
    SELECT CustomerID, Amount,
  DENSE_RANK() OVER (PARTITION BY CustomerID ORDER BY Amount DESC) AS drnk
FROM `project.dataset.Sales`;
    NTILE (Quartiles) 
    SELECT CustomerID, Amount,
  NTILE(4) OVER (ORDER BY Amount DESC) AS quartile
FROM Sales;
    		 
    SELECT CustomerID, Amount,
  NTILE(4) OVER (ORDER BY Amount DESC) AS quartile
FROM Sales;
    		 
    SELECT CustomerID, Amount,
  NTILE(4) OVER (ORDER BY Amount DESC) AS quartile
FROM `project.dataset.Sales`;
    LAG (Prev Row) 
    SELECT OrderDate, Amount,
  LAG(Amount, 1) OVER (ORDER BY OrderDate) AS PrevAmt
FROM Sales;
    		 
    SELECT OrderDate, Amount,
  LAG(Amount, 1) OVER (ORDER BY OrderDate) AS PrevAmt
FROM Sales;
    		 
    SELECT OrderDate, Amount,
  LAG(Amount, 1) OVER (ORDER BY OrderDate) AS PrevAmt
FROM `project.dataset.Sales`;
    LEAD (Next Row) 
    SELECT OrderDate, Amount,
  LEAD(Amount, 1) OVER (ORDER BY OrderDate) AS NextAmt
FROM Sales;
    		 
    SELECT OrderDate, Amount,
  LEAD(Amount, 1) OVER (ORDER BY OrderDate) AS NextAmt
FROM Sales;
    		 
    SELECT OrderDate, Amount,
  LEAD(Amount, 1) OVER (ORDER BY OrderDate) AS NextAmt
FROM `project.dataset.Sales`;
    FIRST_VALUE 
    SELECT OrderDate, Amount,
  FIRST_VALUE(Amount) OVER (
    ORDER BY OrderDate
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS FirstAmt
FROM Sales;
    		 
    SELECT OrderDate, Amount,
  FIRST_VALUE(Amount) OVER (
    ORDER BY OrderDate
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS FirstAmt
FROM Sales;
    		 
    SELECT OrderDate, Amount,
  FIRST_VALUE(Amount) OVER (
    ORDER BY OrderDate
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS FirstAmt
FROM `project.dataset.Sales`;
    LAST_VALUE* 
    SELECT OrderDate, Amount,
  LAST_VALUE(Amount) OVER (
    ORDER BY OrderDate
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS LastAmt
FROM Sales;
    		 
    SELECT OrderDate, Amount,
  LAST_VALUE(Amount) OVER (
    ORDER BY OrderDate
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS LastAmt
FROM Sales;
    		 
    SELECT OrderDate, Amount,
  LAST_VALUE(Amount) OVER (
    ORDER BY OrderDate
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS LastAmt
FROM `project.dataset.Sales`;
    PERCENT_RANK() 
    SELECT Amount,
  PERCENT_RANK() OVER (ORDER BY Amount) AS pct_rank
FROM Sales;
    		 
    SELECT Amount,
  PERCENT_RANK() OVER (ORDER BY Amount) AS pct_rank
FROM Sales;
    		 
    SELECT Amount,
  PERCENT_RANK() OVER (ORDER BY Amount) AS pct_rank
FROM `project.dataset.Sales`;
    CUME_DIST() 
    SELECT Amount,
  CUME_DIST() OVER (ORDER BY Amount) AS cume
FROM Sales;
    		 
    SELECT Amount,
  CUME_DIST() OVER (ORDER BY Amount) AS cume
FROM Sales;
    		 
    SELECT Amount,
  CUME_DIST() OVER (ORDER BY Amount) AS cume
FROM `project.dataset.Sales`;
    Share of Total 
    SELECT CustomerID, Amount,
  Amount * 1.0 / SUM(Amount) OVER (
    PARTITION BY CustomerID
  ) AS ShareOfCust
FROM Sales;
    		 
    SELECT CustomerID, Amount,
  Amount / SUM(Amount) OVER (
    PARTITION BY CustomerID
  ) AS ShareOfCust
FROM Sales;
    		 
    SELECT CustomerID, Amount,
  Amount / SUM(Amount) OVER (
    PARTITION BY CustomerID
  ) AS ShareOfCust
FROM `project.dataset.Sales`;
    COUNT Over Window 
    SELECT CustomerID, OrderDate,
  COUNT(*) OVER (
    PARTITION BY CustomerID
    ORDER BY OrderDate
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS CntToDate
FROM Sales;
    		 
    SELECT CustomerID, OrderDate,
  COUNT(*) OVER (
    PARTITION BY CustomerID
    ORDER BY OrderDate
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS CntToDate
FROM Sales;
    		 
    SELECT CustomerID, OrderDate,
  COUNT(*) OVER (
    PARTITION BY CustomerID
    ORDER BY OrderDate
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS CntToDate
FROM `project.dataset.Sales`;
    Distinct Count in Window 
    -- COUNT(DISTINCT) OVER is not supported.
-- Workaround: distinct per partition via DENSE_RANK.
WITH x AS (
  SELECT CustomerID, ProductID,
         DENSE_RANK() OVER (
           PARTITION BY CustomerID ORDER BY ProductID
         ) AS r
  FROM Sales
)
SELECT CustomerID, MAX(r) AS DistinctProducts
FROM x GROUP BY CustomerID;
    		 
    -- COUNT(DISTINCT) OVER not supported in Snowflake windows.
-- Use DENSE_RANK or COUNT(DISTINCT) without OVER at final grouping.
    		 
    -- COUNT(DISTINCT) OVER not supported in BigQuery windows.
-- Use DENSE_RANK or COUNT(DISTINCT) at GROUP BY level.

    Error Handling

    Error Handling Topic SQL Server Snowflake BigQuery
    Replace NULL with Default 
    SELECT ISNULL(Amount, 0) AS SafeAmt,
       COALESCE(CustomerName, 'Unknown') AS SafeName
FROM Sales;
    		 
    SELECT IFNULL(Amount, 0) AS SafeAmt,
       COALESCE(CustomerName, 'Unknown') AS SafeName
FROM Sales;
    		 
    SELECT IFNULL(Amount, 0) AS SafeAmt,
       COALESCE(CustomerName, 'Unknown') AS SafeName
FROM `project.dataset.Sales`;
    Safe Division by Zero 
    SELECT CASE WHEN Denominator = 0
            THEN NULL
            ELSE Numerator * 1.0 / Denominator END AS Ratio
FROM Data;
    		 
    SELECT DIV0(Numerator, Denominator) AS Ratio
FROM Data;
    		 
    SELECT SAFE_DIVIDE(Numerator, Denominator) AS Ratio
FROM `project.dataset.Data`;
    Safe Cast / Conversion 
    SELECT TRY_CAST(Value AS INT) AS SafeInt
FROM RawData;
    		 
    SELECT TRY_TO_NUMBER(Value) AS SafeInt
FROM RawData;
    		 
    SELECT SAFE_CAST(Value AS INT64) AS SafeInt
FROM `project.dataset.RawData`;
    Null If 
    SELECT NULLIF(Amount, 0) AS NullIfZero
FROM Sales;
    		 
    SELECT NULLIF(Amount, 0) AS NullIfZero
FROM Sales;
    		 
    SELECT NULLIF(Amount, 0) AS NullIfZero
FROM `project.dataset.Sales`;
    Error Catch / Try 
    BEGIN TRY
  SELECT 1/0;
END TRY
BEGIN CATCH
  SELECT ERROR_MESSAGE();
END CATCH;
    		 
    -- Snowflake doesn’t have TRY/CATCH in SQL.
-- Use TRY_* functions (TRY_TO_NUMBER, TRY_CAST) to avoid errors.
    		 
    -- BigQuery doesn’t support TRY/CATCH in SQL.
-- Use SAFE_CAST, SAFE_DIVIDE, IFERROR(expr, alt) in some contexts.
    IfError Style (Return fallback if error) 
    -- Not native in T-SQL.
-- Wrap in TRY/CATCH or use CASE + TRY_CAST.
    		 
    SELECT TRY_TO_NUMBER(Value, 0) AS SafeVal;
    		 
    SELECT IFERROR(1/0, NULL) AS SafeVal;

    Casting

    Casting Topic SQL Server Snowflake BigQuery
    Basic CAST 
    SELECT CAST(Amount AS DECIMAL(12,2)) AS amt_dec;
    		 
    SELECT CAST(Amount AS NUMBER(12,2)) AS amt_dec;
    		 
    SELECT CAST(Amount AS NUMERIC(12,2)) AS amt_dec;
    Alt Syntax 
    SELECT CONVERT(DECIMAL(12,2), Amount) AS amt_dec;
    		 
    SELECT Amount::NUMBER(12,2) AS amt_dec;
    		 
    -- Standard CAST only (no ::)
SELECT CAST(Amount AS NUMERIC(12,2));
    Safe Cast 
    SELECT TRY_CAST(Value AS INT) AS safe_int;
    		 
    SELECT TRY_TO_NUMBER(Value) AS safe_num;
    		 
    SELECT SAFE_CAST(Value AS INT64) AS safe_int;
    String → INT 
    SELECT TRY_CONVERT(INT, '123') AS i;
    		 
    SELECT TRY_TO_NUMBER('123')::INT AS i;
    		 
    SELECT SAFE_CAST('123' AS INT64) AS i;
    String → DECIMAL 
    SELECT TRY_CAST('123.45' AS DECIMAL(10,2)) AS d;
    		 
    SELECT TRY_TO_DECIMAL('123.45',10,2) AS d;
    		 
    SELECT SAFE_CAST('123.45' AS NUMERIC(10,2)) AS d;
    String → DATE (format) 
    -- DD/MM/YYYY
SELECT TRY_CONVERT(date, '03/10/2025', 103);
    		 
    SELECT TO_DATE('03/10/2025','DD/MM/YYYY');
    		 
    SELECT PARSE_DATE('%d/%m/%Y', '03/10/2025');
    String → TIMESTAMP 
    SELECT TRY_CONVERT(datetime2, '2025-10-03T14:05:00Z', 127);
    		 
    SELECT TO_TIMESTAMP_TZ('2025-10-03T14:05:00Z');
    		 
    SELECT TIMESTAMP('2025-10-03T14:05:00Z');
    Epoch Seconds → TS 
    SELECT DATEADD(SECOND, 1696341900, '1970-01-01');
    		 
    SELECT TO_TIMESTAMP(1696341900); -- seconds
    		 
    SELECT TIMESTAMP_SECONDS(1696341900);
    Time Zone Convert 
    SELECT (YourDT AT TIME ZONE 'UTC')
       AT TIME ZONE 'Europe/Malta' AS local_dt;
    		 
    SELECT CONVERT_TIMEZONE('UTC','Europe/Malta', YourTS) AS local_ts;
    		 
    -- Convert UTC timestamp to Europe/Malta datetime
SELECT DATETIME(TIMESTAMP(YourDT), 'Europe/Malta') AS local_dt;
    String → BOOLEAN 
    -- No direct parse; map via CASE
SELECT CASE WHEN LOWER(val) IN ('true','1','y','yes') THEN 1 ELSE 0 END AS bitval;
    		 
    SELECT TRY_TO_BOOLEAN(val) AS b;
    		 
    SELECT SAFE_CAST(val AS BOOL) AS b;
    To String (format) 
    SELECT CONVERT(varchar(10), OrderDate, 23) AS iso_date; -- YYYY-MM-DD
    		 
    SELECT TO_VARCHAR(OrderDate, 'YYYY-MM-DD') AS iso_date;
    		 
    SELECT FORMAT_DATE('%F', OrderDate) AS iso_date;
    String → JSON 
    -- No JSON type. Keep NVARCHAR, use OPENJSON to shred:
SELECT * FROM OPENJSON(@json);
    		 
    SELECT PARSE_JSON('{"a":1,"b":"x"}') AS j;  -- VARIANT
    		 
    SELECT PARSE_JSON('{"a":1,"b":"x"}') AS j;  -- JSON
-- Safe variant:
SELECT SAFE.PARSE_JSON(json_str) AS j;
    Any → JSON String 
    -- Build JSON via FOR JSON:
SELECT * FROM T FOR JSON AUTO;
    		 
    SELECT TO_JSON(OBJECT_CONSTRUCT('a',1,'b','x')) AS json_s;
    		 
    SELECT TO_JSON(STRUCT(1 AS a, 'x' AS b)) AS json_s;
    Date/Time Families 
    -- date, datetime, datetime2, time, smalldatetime
    		 
    -- DATE, TIME, TIMESTAMP_NTZ/LTZ/TTZ
    		 
    -- DATE, DATETIME, TIME, TIMESTAMP

    Joining Tables

    Join Type SQL Server Snowflake BigQuery
    INNER JOIN 
    SELECT c.CustomerName, o.OrderID
FROM Customers c
INNER JOIN Orders o
  ON c.CustomerID = o.CustomerID;
    		 
    SELECT c.CustomerName, o.OrderID
FROM Customers c
JOIN Orders o
  ON c.CustomerID = o.CustomerID;
    		 
    SELECT c.CustomerName, o.OrderID
FROM `project.dataset.Customers` c
JOIN `project.dataset.Orders` o
  ON c.CustomerID = o.CustomerID;
    LEFT JOIN 
    SELECT c.CustomerName, o.OrderID
FROM Customers c
LEFT JOIN Orders o
  ON c.CustomerID = o.CustomerID;
    		 
    SELECT c.CustomerName, o.OrderID
FROM Customers c
LEFT JOIN Orders o
  ON c.CustomerID = o.CustomerID;
    		 
    SELECT c.CustomerName, o.OrderID
FROM `project.dataset.Customers` c
LEFT JOIN `project.dataset.Orders` o
  ON c.CustomerID = o.CustomerID;
    RIGHT JOIN 
    SELECT c.CustomerName, o.OrderID
FROM Customers c
RIGHT JOIN Orders o
  ON c.CustomerID = o.CustomerID;
    		 
    SELECT c.CustomerName, o.OrderID
FROM Customers c
RIGHT JOIN Orders o
  ON c.CustomerID = o.CustomerID;
    		 
    SELECT c.CustomerName, o.OrderID
FROM `project.dataset.Customers` c
RIGHT JOIN `project.dataset.Orders` o
  ON c.CustomerID = o.CustomerID;
    FULL OUTER JOIN 
    SELECT c.CustomerName, o.OrderID
FROM Customers c
FULL OUTER JOIN Orders o
  ON c.CustomerID = o.CustomerID;
    		 
    SELECT c.CustomerName, o.OrderID
FROM Customers c
FULL OUTER JOIN Orders o
  ON c.CustomerID = o.CustomerID;
    		 
    SELECT c.CustomerName, o.OrderID
FROM `project.dataset.Customers` c
FULL OUTER JOIN `project.dataset.Orders` o
  ON c.CustomerID = o.CustomerID;
    CROSS JOIN 
    SELECT c.CustomerName, p.ProductName
FROM Customers c
CROSS JOIN Products p;
    		 
    SELECT c.CustomerName, p.ProductName
FROM Customers c
CROSS JOIN Products p;
    		 
    SELECT c.CustomerName, p.ProductName
FROM `project.dataset.Customers` c
CROSS JOIN `project.dataset.Products` p;
    SELF JOIN 
    SELECT e1.EmployeeName, e2.ManagerName
FROM Employees e1
JOIN Employees e2
  ON e1.ManagerID = e2.EmployeeID;
    		 
    SELECT e1.EmployeeName, e2.ManagerName
FROM Employees e1
JOIN Employees e2
  ON e1.ManagerID = e2.EmployeeID;
    		 
    SELECT e1.EmployeeName, e2.ManagerName
FROM `project.dataset.Employees` e1
JOIN `project.dataset.Employees` e2
  ON e1.ManagerID = e2.EmployeeID;
    Semi Join (Exists) 
    SELECT c.CustomerName
FROM Customers c
WHERE EXISTS (
  SELECT 1 FROM Orders o
  WHERE o.CustomerID = c.CustomerID
);
    		 
    SELECT c.CustomerName
FROM Customers c
WHERE EXISTS (
  SELECT 1 FROM Orders o
  WHERE o.CustomerID = c.CustomerID
);
    		 
    SELECT c.CustomerName
FROM `project.dataset.Customers` c
WHERE EXISTS (
  SELECT 1 FROM `project.dataset.Orders` o
  WHERE o.CustomerID = c.CustomerID
);
    Anti Join (Not Exists) 
    SELECT c.CustomerName
FROM Customers c
WHERE NOT EXISTS (
  SELECT 1 FROM Orders o
  WHERE o.CustomerID = c.CustomerID
);
    		 
    SELECT c.CustomerName
FROM Customers c
WHERE NOT EXISTS (
  SELECT 1 FROM Orders o
  WHERE o.CustomerID = c.CustomerID
);
    		 
    SELECT c.CustomerName
FROM `project.dataset.Customers` c
WHERE NOT EXISTS (
  SELECT 1 FROM `project.dataset.Orders` o
  WHERE o.CustomerID = c.CustomerID
);

    CTE

    CTE Topic SQL Server Snowflake BigQuery
    Basic CTE 
    WITH TopSales AS (
  SELECT CustomerID, Amount
  FROM Sales
  WHERE Amount > 1000
)
SELECT * FROM TopSales;
    		 
    WITH TopSales AS (
  SELECT CustomerID, Amount
  FROM Sales
  WHERE Amount > 1000
)
SELECT * FROM TopSales;
    		 
    WITH TopSales AS (
  SELECT CustomerID, Amount
  FROM `project.dataset.Sales`
  WHERE Amount > 1000
)
SELECT * FROM TopSales;
    Multiple CTEs 
    WITH f AS (
  SELECT * FROM Sales WHERE Amount > 1000
), g AS (
  SELECT CustomerID, SUM(Amount) AS Total
  FROM f GROUP BY CustomerID
)
SELECT * FROM g;
    		 
    WITH f AS (
  SELECT * FROM Sales WHERE Amount > 1000
), g AS (
  SELECT CustomerID, SUM(Amount) AS Total
  FROM f GROUP BY CustomerID
)
SELECT * FROM g;
    		 
    WITH f AS (
  SELECT * FROM `project.dataset.Sales` WHERE Amount > 1000
), g AS (
  SELECT CustomerID, SUM(Amount) AS Total
  FROM f GROUP BY CustomerID
)
SELECT * FROM g;
    CTE with INSERT 
    WITH agg AS (
  SELECT CustomerID, SUM(Amount) AS Total
  FROM Sales GROUP BY CustomerID
)
INSERT INTO CustTotals(CustomerID, Total)
SELECT CustomerID, Total FROM agg;
    		 
    WITH agg AS (
  SELECT CustomerID, SUM(Amount) AS Total
  FROM Sales GROUP BY CustomerID
)
INSERT INTO CustTotals (CustomerID, Total)
SELECT CustomerID, Total FROM agg;
    		 
    WITH agg AS (
  SELECT CustomerID, SUM(Amount) AS Total
  FROM `project.dataset.Sales` GROUP BY CustomerID
)
INSERT INTO `project.dataset.CustTotals` (CustomerID, Total)
SELECT CustomerID, Total FROM agg;
    CTE with UPDATE/DELETE 
    -- UPDATE via CTE
WITH d AS (
  SELECT CustomerID, SUM(Amount) AS Total
  FROM Sales GROUP BY CustomerID
)
UPDATE c
SET c.Total = d.Total
FROM CustTotals c JOIN d ON c.CustomerID = d.CustomerID;

-- DELETE via CTE
WITH old AS (SELECT * FROM Logs WHERE CreatedAt < '2025-01-01')
DELETE FROM old;
    		 
    -- UPDATE via CTE
WITH d AS (
  SELECT CustomerID, SUM(Amount) AS Total
  FROM Sales GROUP BY CustomerID
)
UPDATE CustTotals c
SET Total = d.Total
FROM d
WHERE c.CustomerID = d.CustomerID;

-- DELETE via CTE
WITH old AS (SELECT * FROM Logs WHERE CreatedAt < '2025-01-01')
DELETE FROM Logs USING old WHERE Logs.id = old.id;
    		 
    -- UPDATE via CTE
WITH d AS (
  SELECT CustomerID, SUM(Amount) AS Total
  FROM `project.dataset.Sales` GROUP BY CustomerID
)
UPDATE `project.dataset.CustTotals` c
SET Total = d.Total
FROM d
WHERE c.CustomerID = d.CustomerID;

-- DELETE via CTE
WITH old AS (SELECT id FROM `project.dataset.Logs`
             WHERE CreatedAt < DATE '2025-01-01')
DELETE FROM `project.dataset.Logs` WHERE id IN (SELECT id FROM old);
    Recursive CTE (Hierarchy) 
    WITH EmpCTE AS (
  SELECT EmployeeID, ManagerID, 0 AS lvl
  FROM Employees WHERE ManagerID IS NULL
  UNION ALL
  SELECT e.EmployeeID, e.ManagerID, c.lvl + 1
  FROM Employees e
  JOIN EmpCTE c ON e.ManagerID = c.EmployeeID
)
SELECT * FROM EmpCTE OPTION (MAXRECURSION 100);
    		 
    WITH RECURSIVE EmpCTE AS (
  SELECT EmployeeID, ManagerID, 0 AS lvl
  FROM Employees WHERE ManagerID IS NULL
  UNION ALL
  SELECT e.EmployeeID, e.ManagerID, c.lvl + 1
  FROM Employees e
  JOIN EmpCTE c ON e.ManagerID = c.EmployeeID
)
SELECT * FROM EmpCTE;
    		 
    WITH RECURSIVE EmpCTE AS (
  SELECT EmployeeID, ManagerID, 0 AS lvl
  FROM `project.dataset.Employees`
  WHERE ManagerID IS NULL
  UNION ALL
  SELECT e.EmployeeID, e.ManagerID, c.lvl + 1
  FROM `project.dataset.Employees` e
  JOIN EmpCTE c ON e.ManagerID = c.EmployeeID
)
SELECT * FROM EmpCTE;
    Recursive CTE (Date Series) 
    WITH Dates AS (
  SELECT CAST('2025-01-01' AS date) AS d
  UNION ALL
  SELECT DATEADD(DAY, 1, d) FROM Dates
  WHERE d < '2025-01-31'
)
SELECT * FROM Dates OPTION (MAXRECURSION 0);
    		 
    WITH RECURSIVE Dates AS (
  SELECT TO_DATE('2025-01-01') AS d
  UNION ALL
  SELECT DATEADD(day, 1, d) FROM Dates
  WHERE d < TO_DATE('2025-01-31')
)
SELECT * FROM Dates;
    		 
    WITH RECURSIVE Dates AS (
  SELECT DATE '2025-01-01' AS d
  UNION ALL
  SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM Dates
  WHERE d < DATE '2025-01-31'
)
SELECT * FROM Dates;
    CTE vs Materialization 
    -- CTE is not materialized.
-- Use #temp or @table for reuse:
SELECT ... INTO #t FROM ...;
-- or:
WITH c AS (...) SELECT ... FROM c;
    		 
    -- CTE not materialized.
-- Use TEMP TABLE or transient table:
CREATE TEMP TABLE t AS SELECT ...;
WITH c AS (...) SELECT ... FROM c;
    		 
    -- CTE not materialized by default.
-- BigQuery supports MATERIALIZED CTE hint:
WITH c AS MATERIALIZED (SELECT ... )
SELECT ... FROM c;
    Notes & Scope 
    -- Scope: single statement.
-- Name must be unique within WITH.
-- Recursive needs OPTION(MAXRECURSION ...).
    		 
    -- Scope: single statement.
-- Use WITH RECURSIVE for recursion.
-- Can precede DML/DDL that supports SELECT.
    		 
    -- Scope: single statement.
-- WITH RECURSIVE supported.
-- MATERIALIZED can improve reuse/perf.
  • SQL Window Functions

    SQL Window Functions

    Summary


    1. Aggregate Window Functions
    These functions perform calculations across a set of table rows that are somehow related to the current row

    SUM() OVER(…) – Running total or sum per partition
    AVG() OVER(…) – Average per partition
    COUNT() OVER(…) – Count rows in partition
    MIN() OVER(…) – Minimum value in partition
    MAX() OVER(…) – Maximum value in partition

    2. Ranking Window Functions
    These assign a rank or row number to each row within a partition.

    ROW_NUMBER() OVER(…) – Unique sequential number per row in partition
    RANK() OVER(…) – Rank with gaps for ties
    DENSE_RANK() OVER(…) – Rank without gaps for ties
    NTILE(n) OVER(…) – Divides partition into n buckets and assigns a bucket number

    3. Value Navigation (Analytic) Functions
    These functions return values from other rows in the window frame relative to the current row.

    LAG(expression, offset, default) OVER(…) – Value from a previous row
    LEAD(expression, offset, default) OVER(…) – Value from a following row
    FIRST_VALUE(expression) OVER(…) – First value in the window frame
    LAST_VALUE(expression) OVER(…) – Last value in the window frame
    NTH_VALUE(expression, n) OVER(…) – The nth value in the window frame


    Examples

    1. Aggregate Window Functions

    These perform calculations over a window of rows but return a value for each row.

    NameSalesDate
    Alice1002025-01-01
    Alice1502025-01-03
    Bob2002025-01-01
    Alice502025-01-05
    Bob3002025-01-04


    SUM() OVER() 

    SELECT
  Name,
  Sales,
  SUM(Sales) OVER (PARTITION BY Name ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM SalesData;


    The window function SUM(Sales) OVER (...) calculates a running total of sales for each Name.

    The window is partitioned by Name (so calculations are done separately for each person).

    The rows are ordered by Date within each partition.

    The frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW means the sum includes all rows from the first date up to the current row’s date.

    Output:

    NameSalesRunningTotal
    Alice100100
    Alice150250
    Alice50300
    Bob200200
    Bob300500


    AVG() OVER()

    Customer_idsales
    101200
    102150
    101300
    103400
    102250
    101100
    SELECT 
customer_id, 
sales, 
AVG(sales) OVER (PARTITION BY customer_id) AS AvgSales 
FROM orders;
    • The window function AVG(sales) OVER (PARTITION BY customer_id) calculates the average sales for each customer_id.
    • The average is computed over all rows with the same customer_id.
    • The result is shown on every row corresponding to that customer.
    customer_idsalesAvgSales
    101200200
    101300200
    101100200
    102150200
    102250200
    103400400


    COUNT(*) OVER

    departmentemployee_id
    Sales101
    Sales102
    HR201
    HR202
    HR203
    IT301
    SELECT
  department,
  employee_id,
  COUNT(*) OVER (PARTITION BY department) AS DeptCount
FROM employees;
    • The window function COUNT(*) OVER (PARTITION BY department) counts the total number of employees in each department.
    • This count is repeated on every row for that department.
    • No ordering is required here because the count is the same for all rows in the partition.

    departmentemployee_idDeptCount
    Sales1012
    Sales1022
    HR2013
    HR2023
    HR2033
    IT3011


    2. Ranking Functions

    Assign ranks or row numbers within partitions.

    ROW_NUMBER() — Assigns unique row numbers

    employee_iddepartmentsalary
    101Sales5000
    102Sales7000
    103Sales6000
    201HR4500
    202HR4800
    301IT8000

    SELECT
  employee_id,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS RowNum
FROM employees;

    employee_idsalaryRowNum
    10270001
    10360002
    10150003
    20248001
    20145002
    30180001

    The window function ROW_NUMBER() assigns a unique sequential number to each row within the partition defined by department.

    Rows are ordered by salary in descending order within each department.

    The highest salary in each department gets RowNum = 1, the next highest gets 2, and so on.

    RANK() — Assigns rank, with gaps for ties

    employee_iddepartmentsalary
    101Sales7000
    102Sales7000
    103Sales6000
    201HR4800
    202HR4500
    301IT8000
    302IT8000
    303IT7500
    SELECT
  employee_id,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS Rank
FROM employees;

    The RANK() function assigns a rank to each employee within their department, ordering by salary descending.

    Employees with the same salary get the same rank.

    The next rank after a tie skips the appropriate number of positions (i.e., gaps in ranking).

    employee_idsalaryRank
    10170001
    10270001
    10360003
    20148001
    20245002
    30180001
    30280001
    30375003


    DENSE_RANK() — Like RANK but no gaps

    employee_iddepartmentsalary
    101Sales7000
    102Sales7000
    103Sales6000
    201HR4800
    202HR4500
    301IT8000
    302IT8000
    303IT7500
    SELECT
  employee_id,
  salary,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS DenseRank
FROM employees;
    • DENSE_RANK() assigns ranks within each department, ordered by salary descending.
    • Employees with the same salary get the same rank.
    • Unlike RANK(), DENSE_RANK() does not skip ranks after ties. The next distinct value gets the next consecutive rank.
    employee_idsalaryDenseRank
    10170001
    10270001
    10360002
    20148001
    20245002
    30180001
    30280001
    30375002



    3. Value Navigation Functions

    Access values from other rows relative to the current row.

    LAG() — Value from previous row

    employee_iddepartmentsalary
    101Sales5000
    102Sales6000
    103Sales7000
    201HR4500
    202HR4800
    301IT8000
    SELECT
  employee_id,
  salary,
  LAG(salary, 1) OVER (PARTITION BY department ORDER BY salary) AS PrevSalary
FROM employees;

    LAG(salary, 1) returns the salary value from the previous row within the same department, based on the ascending order of salary.

    For the first row in each department, there is no previous salary, so the result is NULL.

    employee_idsalaryPrevSalary
    1015000NULL
    10260005000
    10370006000
    2014500NULL
    20248004500
    3018000NULL



    LEAD() — Value from next row

    employee_iddepartmentsalary
    101Sales5000
    102Sales6000
    103Sales7000
    201HR4500
    202HR4800
    301IT8000

    SELECT
  employee_id,
  salary,
  LEAD(salary, 1) OVER (PARTITION BY department ORDER BY salary) AS NextSalary
FROM employees;


    LEAD(salary, 1) returns the salary value from the next row within the same department, based on the ascending order of salary.

    For the last row in each department, there is no next salary, so the result is NULL


    5000    		6000
    10260007000
    1037000NULL
    20145004800
    2024800NULL
    3018000NULL


    LAST_VALUE() — Last value in the window frame

    employee_iddepartmentsalary
    101Sales5000
    102Sales7000
    103Sales6000
    201HR4500
    202HR4800
    301IT8000

    SELECT
  employee_id,
  salary,
  LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MaxSalary
FROM employees;
    • LAST_VALUE(salary) returns the last salary value in the ordered partition.
    • The window frame ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ensures the entire partition is considered (not just up to the current row).
    • When ordered by salary ascending, the last value is the highest salary in the department.
    employee_idsalaryMaxSalary
    10150007000
    10360007000
    10270007000
    20145004800
    20248004800
    30180008000

  • An Example of Creating a Sales Report with SQL

    We can create a more interesting sales report using Window functions and additional calculations in the query, including the gross margin %, total profit, and ranks for product price, sales quantity, and sales rank. The query uses a Common Table Expression (CTE) function to group the data first (as we can’t group using Windows functions).

    This is the kind of report decision-makers will be interested in and as more questions come up, additional fields can be added.

    USE [ContosoRetailDW];
-- Note: Qty * UnitPrice - Discount = TotalSalesAmount (small descrepency)
-- CTE

WITH sub1 AS
(
SELECT p.ProductName, s.UnitCost, s.UnitPrice, SUM(SalesQuantity) as TotalSalesQuantity, 
(s.UnitCost * SUM(SalesQuantity)) as TotalCost, SUM(DiscountAmount) as TotalDiscount,
SUM(SalesAmount) as TotalSalesAmount
FROM [dbo].[FactOnlineSales] as s
LEFT OUTER JOIN [dbo].[DimProduct] as p ON s.[ProductKey] = p.[ProductKey]
LEFT OUTER JOIN [dbo].[DimDate] d on s.DateKey = d.DateKey
LEFT OUTER JOIN [dbo].[DimStore] st ON s.StoreKey = st.StoreKey
WHERE 
d.CalendarYear = 2007 AND d.CalendarMonthLabel = 'January'
AND
st.StoreName = 'Contoso Europe Online Store'
GROUP BY  p.ProductName, s.UnitCost, s.UnitPrice
)

--Main Query referencing the CT

SELECT ProductName, UnitCost, UnitPrice, (UnitPrice-UnitCost)/UnitCost as Margin, 
TotalSalesQuantity, format(TotalCost,'$0,,.0M') as TotalCost, format(TotalSalesAmount, '$0,,.0M') as TotalSales, 
format(TotalSalesAmount-TotalCost, '$0,,.0M')  as TotalProfit,
TotalDiscount,
RANK() OVER(ORDER BY UnitPrice DESC) as PriceRank,
RANK() OVER(ORDER BY TotalSalesQuantity DESC) as QtyRank,
RANK() OVER(ORDER BY TotalSalesAmount DESC) as SalesRank
FROM sub1
ORDER BY SalesRank
ORDER BY SalesRank

  • Examples of working with Dates in Google BigQuery

    References:

    Date Functions
    https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions
    Format Elements
    https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_elements_date_time

    Current Date, Last_Date, Construction Add and Subtract

    CURRENT_DATE() as Current_Date, -- returns todays date

DATE(2024,01,24) as Constructed_Date, -- constructs the date from Year, Month, Day

LAST_DAY('2008-12-25') AS Last_Date, -- returns 2008-12-25 the last date

DATE_ADD(DATE "2024-01-24", INTERVAL 1 DAY), -- 1 day later

DATE_SUB(DATE "2024-01-24", INTERVAL 1 DAY), -- 1 day before


    FORMAT_DATE and PARSE_DATE

    SELECT

CURRENT_DATE() as today, -- returns today

FORMAT_DATE('%d/%m/%Y', CURRENT_DATE()) AS formatteday, -- UK format

PARSE_DATE('%d-%m-%Y', '01-05-2024') as passstringdate, -- convert string to date object

FORMAT_DATE('%d/%m/%Y', PARSE_DATE('%d-%m-%Y', '01-05-2024')) AS formatedpassstring -- convert string to date and then format



    EXTRACT

    --CURRENT_DATE() is 15/05/2024

SELECT

EXTRACT(DAY from CURRENT_DATE()) as day, -- 15/05/2024

EXTRACT(DAYOFWEEK from CURRENT_DATE()) as DayOfWeek, -- 4

EXTRACT(WEEK from CURRENT_DATE()) as Week, --19

EXTRACT(WEEK(MONDAY) from CURRENT_DATE()) as WeekStartingMonday, -- 20

EXTRACT(ISOWEEK from CURRENT_DATE()) as WeekStartingMonday2, -- 20

EXTRACT(MONTH from CURRENT_DATE()) as  Mth, -- 5

EXTRACT(QUARTER from CURRENT_DATE()) as Qtr, -- 2

EXTRACT(YEAR from CURRENT_DATE()) as Yr, -- 2024

EXTRACT(ISOYEAR from CURRENT_DATE()) as YrWkStartMon -- 2024


    DATE_DIFF

    SELECT DATE_DIFF(DATE '2024-01-25', DATE '2024-01-02', DAY) AS days_diff -- returns 23 (note format end_date, start_date)

    DATE() Constructor

    SELECT *
FROM `bigquery-public-data.google_trends.top_terms`
WHERE rank = 1
--Date Constructor
AND refresh_date >= DATE(2024, 01, 01)
LIMIT 10


    DATE TRUNC()

    SELECT *

FROM `bigquery-public-data.google_trends.top_terms`

WHERE rank = 1
-- DATE_SUB subtracts from given date

AND refresh_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 2 WEEK)

-- Limit result to 10 (works like TOP)

LIMIT 10






  • SQL Code for Lifetime Value Model

    The first part of building the code to pull the data for the lifetime value model is to create an SQL script that will get the historical data we need to train our model on.

    The SQL Code select 6 fields from the Contoso Retail Datawarehouse:
    The full SQL code is at the bottom of the page

    First Section of Code (WITH)

    USE ContosoRetailDW;

WITH FirstTransactionDates AS (
    SELECT customerkey,MIN([DateKey]) AS first_transaction_date
    FROM [dbo].[FactOnlineSales]
    GROUP BY customerkey)
	,
	Orders AS (SELECT 
		  [CustomerKey] as CustomerKey, 
		  [SalesOrderNumber] as SalesOrderNumber,
		  ROW_NUMBER() OVER (PARTITION BY [CustomerKey] ORDER BY [SalesOrderNumber] ASC) AS OrderNumber,
		  SUM([SalesAmount]) as SalesAmount
		  FROM
		  [dbo].[FactOnlineSales]
		  GROUP BY [CustomerKey], [SalesOrderNumber] 
)


    The code starts off with a subquery after the database is selected in the USE statement.

    2 Sub-queries are then created in the WITH clause. This makes the code easier to manage rather than using sub-queries on sub-queries in the main code body.

    The first sub-query: FirstTransactionDates creates a temporary table of customers and their first transaction date from the FactOnlineSales table

    The second-subquery: Orders creates a temporary table of customers and their orders with the sales ordernumber field ranking the orders from 1 upwards with 1 being the first order. I will create a separate post explaining the line:
    ROW_NUMBER() OVER (PARTITION BY [CustomerKey] ORDER BY [SalesOrderNumber] ASC) AS OrderNumber,


    Second Section of Code

    SELECT 
--selection of CustomerKey from DimCustomer
c.CustomerKey as CustomerKey, 

-- Calculate Age as difference between [BirthDate] selected from DimCustomer and today (using the GETDATE() function), then divide by 365
DATEDIFF(DAY, [BirthDate], GETDATE())/365 as Age, 

--Gender selected as the [gender] field from DimCustomer and converted to 0 and 1 for m and f respectively
CASE 
WHEN [gender] = 'm' THEN 0
WHEN [gender] = 'f' THEN 1
END as Gender,

--YearlyIncome selected from DimCustomer table
c.YearlyIncome as YearlyIncome, 

--FirstPurchaseAmount from sub-query a
a.FirstPurchaseAmount as FirstPurchaseAmount,

LifetimeSales as sub-query b
b.first12months_salesamount as LifetimeSales

-- select from DimCustomer with left join on.... tables to follow...
FROM dbo.DimCustomer c LEFT OUTER JOIN


    As you can see from the above, there are 6 field selections:
    1. The CustomerKey from the DimCustomer table (alias c)
    2. Age calculated as the difference in days between the customers birthdate ([BirthDate] and todays date (GETDATE()). This is then divided by 365 to get the age in years.
    3. The Gender which uses the [gender] filed from the DimCustomer table and swaps ‘m’ for 0 and ‘f’ for 1, so we have numeric data for the model.
    4. The YearlyIncome field from the DimCustomer table.

    Then the last 2 fields FirstPurchaseAmount and first12months_salesamount which are calculated using sub-queries.

    Third Section of Code (Sub-query a)

    (
SELECT CustomerKey, SalesAmount as FirstPurchaseAmount
FROM ORDERS
WHERE OrderNumber = 1
) a
ON c.CustomerKey = a.CustomerKey



    First Sub-query a, which is the DimCustomer table joins to on ON c.CustomerKey = a.CustomerKey
    The SELECT query select, CustomerKey and SalesAmount from the ORDERS table.
    The ORDERS table is a temporary table created with a sub-query in the WITH clause at the start of the code. The ORDERS table is filtered by OrderNumber = 1 to return the sum of sales for each customer on their first order. This gives us the FirstPurchaseAmount field.

    The Fourth Section of code is sub-query B

    LEFT OUTER JOIN

-- Build First 12 Month Sales Table b
(
SELECT 
    f.customerkey,
    SUM(CASE WHEN DATEDIFF(month, ft.first_transaction_date, f.DateKey) < 12 THEN f.salesamount 
	ELSE 0 
	END) AS first12months_salesamount

FROM [dbo].[FactOnlineSales] f
LEFT OUTER JOIN FirstTransactionDates ft ON 
f.customerkey = ft.customerkey
GROUP BY f.CustomerKey
) as b
ON c.CustomerKey = b.CustomerKey

WHERE c.CustomerType = 'Person'


    The fourth section of code shows the LEFT OUTER JOIN of the DimCustomer table to sub-query b.
    Sub-query b gets us the first 12 month sales amount of customers, which will be our dependent variable in our final Model.

    The SELECT code starts by selecting the CustomerKey from the FactOnlineSales table.

    The second line selects the first_transaction_date from the FirstTransactionDates sub-query created in the WITH clause and the f.DateKey from the FactOnlineSales table and looks at the difference in months between the 2. If the DateKey (transaction date is less than 12 months), then the sales amount is summed as first12months_salesamount.
    b.first12months_salesamount as LifetimeSales

    SUM(CASE WHEN DATEDIFF(month, ft.first_transaction_date, f.DateKey) < 12 THEN f.salesamount
    ELSE 0
    END) AS first12months_salesamount

    This is the field used in the main SELECT query in code section 2.
    b.first12months_salesamount as LifetimeSales

    Full SQL Code

    USE ContosoRetailDW;

WITH FirstTransactionDates AS (
    SELECT customerkey,MIN([DateKey]) AS first_transaction_date
    FROM [dbo].[FactOnlineSales]
    GROUP BY customerkey)
	,
	Orders AS (SELECT 
		  [CustomerKey] as CustomerKey, 
		  [SalesOrderNumber] as SalesOrderNumber,
		  ROW_NUMBER() OVER (PARTITION BY [CustomerKey] ORDER BY [SalesOrderNumber] ASC) AS OrderNumber,
		  SUM([SalesAmount]) as SalesAmount
		  FROM
		  [dbo].[FactOnlineSales]
		  GROUP BY [CustomerKey], [SalesOrderNumber] 
)

SELECT 
c.CustomerKey as CustomerKey, 
DATEDIFF(DAY, [BirthDate], GETDATE())/365 as Age, 
CASE 
WHEN [gender] = 'm' THEN 0
WHEN [gender] = 'f' THEN 1
END as Gender,
c.YearlyIncome as YearlyIncome, 
a.FirstPurchaseAmount as FirstPurchaseAmount,
b.first12months_salesamount as LifetimeSales

FROM 
dbo.DimCustomer c

--Build FirstPurchaseAmount table a
LEFT OUTER JOIN
(
SELECT CustomerKey, SalesAmount as FirstPurchaseAmount
FROM ORDERS
WHERE OrderNumber = 1
) a
ON c.CustomerKey = a.CustomerKey

LEFT OUTER JOIN

-- Build First 12 Month Sales Table b
(
SELECT 
    f.customerkey,
    SUM(CASE WHEN DATEDIFF(month, ft.first_transaction_date, f.DateKey) < 12 THEN f.salesamount 
	ELSE 0 
	END) AS first12months_salesamount

FROM [dbo].[FactOnlineSales] f
LEFT OUTER JOIN FirstTransactionDates ft ON 
f.customerkey = ft.customerkey
GROUP BY f.CustomerKey
) as b
ON c.CustomerKey = b.CustomerKey

WHERE c.CustomerType = 'Person'

  • How to install the Contoso Retail Database on SQL Server

    There are three steps to get up and running with a Contoso Retail Datawarehouse that you can use for experimenting with Power BI.

    Step 1. If you don’t already have access to an installation of Microsoft SQL server you can install one on your local machine.

    There are 2 free non-production versions available:

    Microsoft SQL Server Express – This includes just the database engine required to get up and run with the database.

    Microsoft SQL Server Developer – In addition to the database engine, this also includes SQL Server Analysis services, reporting services, integration services, and Machine Learning services. It’s over a GB, so a larger installation than the Express version. Of note, Analysis services is included in the Developer version, so it’s useful to install this version.

    Microsoft SQL Server Express and Developer versions are available with instructions here.
    Installation is very straightforward, just follow the instructions.

    Step 2. Install SQL Server Management Studio (SSMS). You will use this tool to connect to the SQL Server installation and run queries on the database.
    You can also download this from Microsoft – here.

    This is the connection information for connecting to the SQL Server developer on my local machine. Note for me if I use localhost\MSSQLSERVER for the server name I get an error. It works on localhost only

    For further troubleshooting, if you open the services app, you can check that services are running and start as required (Run > Services.exe)

    If you connect ok, you should see a screen like this one. You only need to worry about the Databases folder, so don’t worry about all the other stuff for now. Note: This is the Developer version, the Express version, will be slightly different.


    Step 3: The Contoso Retail Datawarehouse database can be downloaded from Microsoft here.
    There are two files: ContosoBIdemoABF.exe and ContosoBIdemoBAK.exe .

    The ContosoRetailDW.bak file is the raw database, which you can get started with and only requires the database engine (which is included in SQL Server Express).

    The ContosoBIdemoABF.exe is the OLAP version of the database, which requires SQL Server Analysis Services. For this, you will need either a full version or the free developer version.

    To install the first file. Connect to the SQL Server Database Engine (Open SSMS and select database engine). Right-click on Databases and select ‘Restore Database’.

    Select Device from the next screen.

    You can then select ‘Add’ to add the file. I placed the ContosoRetailDW.bak file in my C: drive to make it easier to find:

    Then just select the file and click OK, OK, and if all goes well, the database will be restored.


    Now to check it is really there, just return to the main screen, spin open the ContosoRetailDW database, and check the table is there.


    Now you can start running some queries against it and you can connect the Power BI desktop to it.


  • An Example investigation of the Contoso Data Warehouse Tables


    For installing and getting started with the Contoso Retail Database, please see this article.

    A standard starting point for creating customer insight reports is to combine data from sales with your customer table. In the Contoso Retail DW, we have the FactSales and DimCustomer tables that we can use. The DimCustomer Table has a field called Customerkey, but if we look in the main FactSales table we cannot see a Customerkey.

    So what we can do is search all the tables for tables with a field name like ‘CustomerKey’.

    Here is the code: 

    SELECT c.name AS 'FieldName'
,(SCHEMA_NAME(t.schema_id) + '.' + t.name) AS 'TableName'
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE 'CustomerKey%'
ORDER BY FieldName, TableName

    The output is below:


    We can see FactOnlineSales table does have a CustomerKey.

    There is a CustomerType field in the DimCustomer table which segments the data into 2 different types: Person and Company. 

    SELECT customerType
FROM DimCustomer
GROUP BY CustomerType


    Going back to the FactSales table, we can see there is a StoreKey field, which looks useful and there is a table called DimStore.

    A basic join across these tables (see below) returns 306 store names, so we know that we can at least work at the store level. Whether online sales data includes customer-level data is still to be investigated. If so, this will help us structure the reports, so we will potentially have Store vs. Online (with online presumably being a single website for the Contoso company). At this stage, we don’t know yet.

    SELECT st.StoreName, SUM(s.SalesAmount) as SalesAmount
FROM
[ContosoRetailDW].[dbo].[DimStore] st LEFT OUTER JOIN dbo.FactSales s
ON s.[StoreKey] = st.[StoreKey]
GROUP BY st.StoreName



    With this new information, we can import the DimStore Table as we know we are going to use that in the report. In this example, we are importing the table directly into the report, but with the Power BI service, we would want to set up the table imports into the Power BU dataflows, so they become available to all reports.

    On exploration of the DimStore table, find there is a StoreType field that has 4 rows:
    Catalog,
    Online,
    Reseller,
    Store.

    We can now have a look at sales under these new segments, to see how relevant they are:

    We can use the following query:

    SELECT st.StoreType, format(SUM(s.SalesAmount),'$0,,M') as SalesAmount
FROM
[ContosoRetailDW].[dbo].[DimStore] st LEFT OUTER JOIN dbo.FactSales s
ON s.[StoreKey] = st.[StoreKey]
GROUP BY st.StoreType


    This gives us a really good new understanding of how the business is organized.
    We can see that there as well as sales via Catalog, Online, and Reseller, there are also Store sales, which may indicate that the database includes sales by store as opposed to sales by customer.

    Now, let’s go back to that question about individual customer-level data.
    The following query will return sales from the FactOnlineSales table by Customer Type

    Select [CustomerType], format(SUM(o.SalesAmount),'$0,,M') as SalesAmount
from
[ContosoRetailDW].[dbo].[DimCustomer] c
LEFT OUTER JOIN [ContosoRetailDW].[dbo].[FactOnlineSales] o
ON c.CustomerKey =o.CustomerKey
GROUP BY [CustomerType]


    So we can see that sales in the FactOnlineSales table by company are over 3 times that of person.

    Let’s see if the FactSales Table contains data in the FactOnlineSales table
    When we try to join the FactSales table to the FactOnlineSales table, we find there is no relationship between the SalesKey and OnlineSalesKey, which we might expect.

    SELECT
[SalesKey]
FROM
[ContosoRetailDW].[dbo].[FactSales] s
INNER JOIN [dbo].[FactOnlineSales] o
ON s.[SalesKey] = o.[OnlineSalesKey]


    The output is blank so there is no relationship here.


    So, let’s take all the online sales from the FactSales table by Date and see how closely this matches the sales by date of the FactOnlineSales Table:
    We have 2 queries:


    FactSales

    SELECT
[SalesKey]
FROM
[ContosoRetailDW].[dbo].[FactSales] s
INNER JOIN [dbo].[FactOnlineSales] o
ON s.[SalesKey] = o.[OnlineSalesKey]

    FactOnlineSales

    SELECT [DateKey], SUM(SalesAmount) as SalesAmount
FROM
[ContosoRetailDW].[dbo].[FactOnlineSales]
group by [DateKey]


    If we grab the output of these queries and graph them in Excel, we can see the sales are similar, but with differences in the 2 tables.



    So now, we just need to figure out what the differences are. Again, if we could reach out to someone in the company, that would be great, but we can’t so we need to figure it out ourselves.

    The next step is to get a bit more granular, so select a specific date from the 2 tables and see if we can figure out the differences.

    Here is the code. We’re going to see if filtering both tables by the Store name= ‘Contoso Europe Online Store’. This will give us matching sales across the 2 tables.

    SELECT d.StoreName, SUM(SalesAmount) as SalesAmount
FROM
[ContosoRetailDW].[dbo].[FactOnlineSales] s
LEFT OUTER JOIN [ContosoRetailDW].[dbo].[DimCustomer] c
ON s.CustomerKey=c.CustomerKey
LEFT OUTER JOIN [ContosoRetailDW].[dbo].[DimStore] d
ON s.StoreKey = d.StoreKey
WHERE DateKey = '2009-01-01' AND d.StoreName = 'Contoso Europe Online Store'
group by d.StoreName

    SELECT s.DateKey, SUM(s.SalesAmount) as SalesAmount
FROM
[ContosoRetailDW].[dbo].[FactSales] s LEFT OUTER JOIN [ContosoRetailDW].[dbo].[DimStore] st
ON s.StoreKey = st.StoreKey
WHERE StoreType = 'Online' AND s.DateKey = '2009-01-01' AND st.StoreName = 'Contoso Europe Online Store'
GROUP BY s.DateKey


    The first query output is as follows:

    StoreName SalesAmount
    Contoso Europe Online Store 432284.288


    The second query output is:

    DateKey SalesAmount
    2009-01-01 00:00:00.000 521675.102

    So that’s a shame there is still a big difference between the 2 there. The next step seems to be go down to the product level, so we try the following two queries:

    Both tables also include two fields of interest: ReturnAmount and Discount. It would be good to check they are similar across tables. We can run both queries together.

    SELECT SUM(s.SalesAmount) as SalesAmount, SUM(s.[ReturnAmount]) as ReturnAmount, SUM(s.[DiscountAmount])
 as DiscountAmount
 FROM
[ContosoRetailDW].[dbo].[FactSales] s LEFT OUTER JOIN [ContosoRetailDW].[dbo].[DimStore] st
ON s.StoreKey = st.StoreKey
WHERE StoreType = 'Online' AND s.DateKey = '2009-01-01' AND st.StoreName = 'Contoso Europe Online Store'
GROUP BY s.DateKey

SELECT SUM(s.SalesAmount) as SalesAmount, SUM(s.[ReturnAmount]) as ReturnAmount, SUM(s.[DiscountAmount])
 as DiscountAmount
 FROM
[ContosoRetailDW].[dbo].[FactOnlineSales] s LEFT OUTER JOIN [ContosoRetailDW].[dbo].[DimStore] st
ON s.StoreKey = st.StoreKey
WHERE StoreType = 'Online' AND s.DateKey = '2009-01-01' AND st.StoreName = 'Contoso Europe Online Store'
GROUP BY s.DateKey


    What we see here is that the return amounts are similar but the discount amounts on the FactOnlineSales table are over 3x that of the FactSales table.



    The first thing I do is add the ReturnAmount and the DiscountAmount to both queries.
    The discount amount from the first two products ordered by sales amount is much higher in the FactOnlineSales table than in the FactSales table. If I add the discount amount and return amount to the sales amount in each table, we it gets much closer.


    That’s it for now, I’m pretty convinced all the sales in FactOnlineSales are in the FactSales table.
    As the FactOnlineSales key includes a CustomerKey, we can use this table for customer-level analysis, but bearing in mind it is Online sales data only.



  • An Overview of the Contoso Retail Datawarehouse Tables

    The learn power bi we need data to work with. The Contoso Retail DW database from Microsoft is freely available, so we’ll use that one. You could just install a copy to a local desktop version of SQL Express.
    A description of the dataset from Microsoft is as follows:

    ‘The Contoso BI Demo dataset is used to demonstrate DW/BI functionalities across the entire Microsoft Office product family. This dataset includes C-level, sales/marketing, IT, and common finance scenarios for the retail industry and support map integration. In addition, this dataset offers large volumes of transactions from OLTP and well-structured aggregations from OLAP, along with reference and dimension data’.

    So although, when you work in business, the data warehouse will be different. In a Retail business, it will include various similarities that that of the Contoso data warehouse. Indeed there will still be similarities across other types of organisations, such as Business to Business (B2B) and Charitable organisations. They will all for example have a customer table and a sales/revenue table. Customers will have similar characteristics, such as their first order date, and last order date, and demographics, such as age, gender, and location. Field names might be different, but there will be similarities. Some organizations will have additional datasets bespoke to their industry. Online gambling companies, for example, will have tables for bonus amounts, although this might be compared to rewards given by retail companies.
    Another example is VIP/loyalty levels which are also used in many industries. Supermarkets have loyalty programs, Charities, and B2B organizations will identify high-level donors or high-value customers. Gambling Companies will have high-value losing customers.

    So let’s have a look at the Contoso Retail Datawarehouse and see what’s in it:

    Dimension Tables


    dbo.DimAccount
    SELECT TOP 100 * FROM dbo.DimAccount
    Inspecting the information we can see a list of accounts. This will likely have been pulled from an accounting (Finance Department).

    dbo.DimChannel
    SELECT TOP 100 * FROM dbo.DimChannel
    This table is just a list of 4 channels: Store, Online, Catalog, and Reseller. We would expect to use this information when querying transaction information to add what channel the transactions are from. We would expect to join using the ChannelKey field.

    dbo.DimCurrency
    SELECT TOP 100 * FROM dbo.DimCurrency
    Again a similar table to DimChannel but for currency. We would expect a sales table to include the currency key to which we can join on to add currency to the query.

    dbo.DimCustomer
    SELECT TOP 100 * FROM dbo.DimCustomer
    The most important table (in addition to sales) in the data warehouse. The big list of businesses. customers. We would expect to join this to a sales table, to report on what customers are buying.
    Fields in this table include gender, dob, email address, address, phone numbers, and first purchase date. All sensitive personal information, so you need to think about how you work with this data and who has access to it.

    dbo.DimDate
    SELECT TOP 100 * FROM dbo.DimDate
    An extremely useful and important table, that you would expect to be working with a lot in your reporting. By joining the table to sales orders, you can then not just the relevant Calendar Year/Quarter/Month, but the Financial Year, quarter, month, and week, which are often specific to the organization.

    dbo.DimEmployee
    SELECT TOP 100 * FROM dbo.DimEmployee
    A useful table of employee information likely used for a variety of report requirements, not just related to pay, but maybe related to sales.

    dbo.DimEntity
    SELECT TOP 100 * FROM dbo.DimEntity
    A table of global region and country sites, which are relevant to multinational organizations, used for Global group reporting.

    dbo. DimGeography
    SELECT TOP 100 * FROM dbo.DimGeography
    A table with countries and regions, is probably used to classify countries by Global region in some way.


    dbo.DimMachine
    SELECT TOP 100 * FROM dbo.DimMachine
    Appears to relate to certain types of equipment in stores.

    dbo.DimOutage
    SELECT TOP 100 * FROM dbo.DimOutage
    A table of computer outages, possibly related to DimMachine

    dbo.DimProduct
    SELECT TOP 100 * FROM dbo.DimProduct
    A key table, listing all the products presumably for sales, with a product key to join it to the sales table.

    dbo.DimProductCategory
    SELECT TOP 100 * FROM dbo.DimProductCategory
    A table one would expect to join to DimProduct to allow for category level reporting.

    dbo.DimProductSubCategory
    SELECT TOP 100 * FROM dbo.DimProductSubCategory
    Similar to dbo.DimProductCategory but presumably the subcategory level

    dbo.DimPromotion
    SELECT TOP 100 * FROM dbo.DimPromotion
    A table of promotions, we can join it to the sales table and add marketing information to sales

    dbo.DimSalesTerritory
    SELECT TOP 100 * FROM dbo.DimSalesTerritory
    A table of territories, presumably relevant to the sales team, which may also join to dbo.DimEmployee

    dbo.DimScenario
    SELECT TOP 100 * FROM dbo.DimScenario
    A Finance table with keys for Actual, Budget, and Forecast. There should be sales information we can join to, to determine what type of data it is (Actual, Budget, and forecast). Top-level reporting for example will want to show actual revenue against budget and forecast.

    dbo.DimStore
    SELECT TOP 100 * FROM dbo.DimStore
    A table of store names and addresses.

    Fact Tables

    dbo.FactExchangeRate
    SELECT TOP 100 * FROM dbo.FactExchangeRate
    A table of exchange rates. With a multi-national organization, the business will want to be able to report in Global sales (perhaps in dollars), so this table will be key. One would expect this to be updated on a regular basis, but it might be just once a quarter.

    dbo.FactInventory
    SELECT TOP 100 * FROM dbo.FactInventory
    An essential table keeping a count of all products in stock.
    A table relating to the costs of machines (presumably check-outs)

    dbo.FactITSLA
    SELECT TOP 100 * FROM dbo.FactITSLA
    A table with information about the uptime of machines

    dbo.FactOnlineSales
    SELECT TOP 100 * FROM dbo.FactOnlineSales
    A transaction table of online sales

    dbo.FactSales
    SELECT TOP 100 * FROM dbo.FactSales
    The most important table of all (along with the customers). The transaction table, possibly including the Online sales data. More investigation is needed.

    FactSalesQuota
    SELECT TOP 100 * FROM dbo.FactSalesQuota
    The sales quotas by store, with scenarios set next to them (Actual, Budget, Forecast), are presumably used for reporting on what stores are hitting their targets.

    dbo.FactStrategyPlan
    SELECT TOP 100 * FROM dbo.FactStrategyPlan
    An empty table

    So that’s a brief look at all the tables. As you can see we’ve covered data relevant to C-Suite (targets, store performance, global/regional sales), Sales (transaction/region/customer, employee/quotas), Marketing (Promotions), Finance (Accounts, Sales Quotas, exchange rates, costs).