Author: CB

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;

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`;

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);

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;

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;

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`;

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 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 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 FROM Customers WHERE CustomerID = 1;

DELETE FROM Customers WHERE CustomerID = 1;

DELETE FROM dataset.Customers WHERE CustomerID = 1;

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`;

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`;

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`;

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`;

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;

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`;

SELECT DATEFROMPARTS(2025, 10, 3) AS d;

SELECT DATE_FROM_PARTS(2025, 10, 3) AS d;

SELECT DATE(2025, 10, 3) AS d;

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';

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;

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`;

SELECT EOMONTH(OrderDate)
FROM Orders;

SELECT LAST_DAY(OrderDate, 'MONTH')
FROM Orders;

SELECT LAST_DAY(OrderDate, MONTH)
FROM `project.dataset.Orders`;

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`;

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`;

SELECT DATEDIFF(DAY, OrderDate, ShippedDate)
FROM Orders;

SELECT DATEDIFF('DAY', OrderDate, ShippedDate)
FROM Orders;

SELECT DATE_DIFF(ShippedDate, OrderDate, DAY)
FROM `project.dataset.Orders`;

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;

-- 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`;

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`;

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`;

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`;

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`;

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`;

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';

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);

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`;

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`;

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`;

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`;

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`;

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`;

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`;

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`;

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`;

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`;

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`;

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`;

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`;

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`;

-- 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.

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`;

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`;

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`;

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`;

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.

-- 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;

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;

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));

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;

SELECT TRY_CONVERT(INT, '123') AS i;

SELECT TRY_TO_NUMBER('123')::INT AS i;

SELECT SAFE_CAST('123' AS INT64) AS i;

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;

-- 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');

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');

SELECT DATEADD(SECOND, 1696341900, '1970-01-01');

SELECT TO_TIMESTAMP(1696341900); -- seconds

SELECT TIMESTAMP_SECONDS(1696341900);

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;

-- 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;

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;

-- 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;

-- 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, datetime, datetime2, time, smalldatetime

-- DATE, TIME, TIMESTAMP_NTZ/LTZ/TTZ

-- DATE, DATETIME, TIME, TIMESTAMP

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;

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;

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;

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;

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;

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;

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
);

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
);

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;

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;

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;

-- 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);

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;

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 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;

-- 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.