Hi @pmscorca,
There are several SQL queries that you can use for stresstesting. You can think of:
Reporting Queries with Complex Aggregations
SELECT
CustomerID,
SUM(TotalAmount) AS TotalSpent,
COUNT(OrderID) AS NumberOfOrders,
AVG(TotalAmount) AS AverageOrderValue
FROM Orders
WHERE OrderDate BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY CustomerID
ORDER BY TotalSpent DESC;
Recursive Common Table Expression (CTE)
WITH RecursiveOrgChart AS (
SELECT EmployeeID, ManagerID, 1 AS Level
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.ManagerID, roc.Level + 1
FROM Employees e
INNER JOIN RecursiveOrgChart roc ON e.ManagerID = roc.EmployeeID
)
SELECT *
FROM RecursiveOrgChart
ORDER BY Level;
Window Functions over Large Data Sets
SELECT
EmployeeID,
Salary,
RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS SalaryRank
FROM Employees
WHERE HireDate BETWEEN '2000-01-01' AND '2023-01-01';
Lag and Lead Functions
SELECT
StockID,
PriceDate,
ClosePrice,
LAG(ClosePrice, 1) OVER (PARTITION BY StockID ORDER BY PriceDate) AS PreviousClosePrice,
LEAD(ClosePrice, 1) OVER (PARTITION BY StockID ORDER BY PriceDate) AS NextClosePrice,
(ClosePrice - LAG(ClosePrice, 1) OVER (PARTITION BY StockID ORDER BY PriceDate)) AS PriceChange,
(LEAD(ClosePrice, 1) OVER (PARTITION BY StockID ORDER BY PriceDate) - ClosePrice) AS PredictedChange
FROM StockPrices
WHERE PriceDate BETWEEN '2023-01-01' AND '2023-12-31'
ORDER BY StockID, PriceDate;
