Amazon Redshift Architecture and Its Components

Leader Nodes

It keeps all the important information about tables and how the cluster is set up.
It decides the fastest way to run your query and sends the plan to all compute nodes.
It acts as the middleman between your SQL tools and the compute nodes.
It breaks the work into smaller pieces and sends them to the right slices to process.
It reads your SQL query, understands it, and figures out the best way to run it.
It collects the partial results coming from compute nodes and combines them into the final answer.
It makes sure many users can run queries at the same time without conflicts and keeps data consistent.
It stores all important information about tables, schemas, and cluster configuration.
It spreads the work evenly across compute nodes so no node gets overloaded.
It monitors the health of the cluster and handles node failures or issues.
It acts as the single connection point for all SQL tools and applications.
It breaks your SQL query into smaller tasks that can run in parallel.
It controls how data is distributed across nodes — by key, evenly, or fully copied.
It ensures all compute nodes work together smoothly and stay in sync during query execution.

Compute Nodes

Compute nodes store all the actual user data in the Redshift cluster.
They receive tasks from the leader node and process their assigned portion of the data.
Each compute node has its own CPU, memory, and storage to perform heavy workloads.
They run query operations in parallel and send results back to the leader node.
Compute nodes divide their work across slices for even faster processing.
They handle scanning, filtering, joining, and aggregating data locally.
The performance of the cluster increases as more compute nodes are added.
Compute nodes work independently but stay coordinated through the leader node.
They ensure large datasets can be processed quickly through parallel execution.
All user tables and column data physically reside on compute nodes, not on the leader node.

Node Slices

Each compute node is divided into smaller workers called slices.
Every slice gets its own portion of the node’s CPU, memory, and storage.
Slices work independently and in parallel to process data faster.
When data is loaded, rows are spread across slices based on the distribution key.
Each slice handles only its assigned chunk of data, reducing workload per slice.
More slices mean more parallelism, which improves query performance.
The number of slices depends on the node type (larger nodes = more slices).
Slices perform tasks like scanning, filtering, and joining data locally.
They send their partial results back to the compute node, which forwards them to the leader node.
Slices ensure that Redshift uses every bit of hardware inside a compute node efficiently.


Amazon Redshift Architecture:

What Happens When a User Runs a Simple SELECT Query

The leader node receives the SELECT query from the user.
It parses the SQL to understand what the user wants.
It builds a query tree (a logical structure of the query).
The optimizer checks statistics and finds the fastest way to run the query.
It creates the execution plan (the step‑by‑step blueprint).
The execution engine turns the plan into compiled C++ code.
Compute nodes run the code in parallel, each working on its portion of the data.
The leader node combines the results and returns the final output to the user.

The Art of SQL Practice: 12 Dimensions of Real‑World Data Analysis

✅ SECTION 1 — Filtering Data (Topics 1–10)

Filtering is the foundation of . Every ETL pipeline, report, and transformation starts with selecting the right rows.

1. Filter rows with simple conditions

What it is

Use WHERE to return only rows that match a condition.

Syntax

SELECT columns FROM table WHERE condition;

Example

Customers from USA:

SELECT * FROM Sales.Customers WHERE Country = 'USA';

Practice

Get all orders where Sales > 500.

2. Filter with AND

What it is

Combine multiple conditions that must all be true.

Example

Orders with high sales AND completed status:

SELECT * FROM Sales.Orders WHERE Sales > 500 AND OrderStatus = 'Completed';

Practice

Find customers from India with Score > 80.

3. Filter with OR

What it is

Return rows where any condition is true.

Example

Orders that are either Pending or Cancelled:

SELECT * FROM Sales.Orders WHERE OrderStatus = 'Pending' OR OrderStatus = 'Cancelled';

Practice

Find customers from USA or Canada.

4. Filter with BETWEEN

What it is

Filter values within a range (inclusive).

Example

Orders placed in May 2023:

SELECT * FROM Sales.Orders WHERE OrderDate BETWEEN '2023-05-01' AND '2023-05-31';

Practice

Find customers created between two dates.

5. Filter with IN

What it is

Match against a list of values.

Example

Orders with specific statuses:

SELECT * FROM Sales.Orders WHERE OrderStatus IN ('Completed', 'Pending');

Practice

Find customers from a list of countries.

6. Filter with LIKE

What it is

Pattern matching for strings.

Example

Customers whose first name starts with “A”:

SELECT * FROM Sales.Customers WHERE FirstName LIKE 'A%';

Practice

Find customers whose email ends with “.com”.

7. Filter NULL values

What it is

Use IS NULL or IS NOT NULL.

Example

Orders missing a ShipDate:

SELECT * FROM Sales.Orders WHERE ShipDate IS NULL;

Practice

Find customers with no phone number.

8. Filter NOT conditions

What it is

Exclude values.

Example

Orders not shipped to USA:

SELECT * FROM Sales.Orders WHERE Country <> 'USA';
(If you add Country to Orders later.)

Practice

Find customers not from India.

9. Filter using expressions

What it is

Use calculations inside WHERE.

Example

Orders where total revenue (Sales × Quantity) > 1000:

SELECT * FROM Sales.Orders WHERE (Sales * Quantity) > 1000;

Practice

Find orders where Quantity × Sales < 500.

10. Combine multiple filters

What it is

Use parentheses to control logic.

Example

Orders that are Completed OR (Pending AND high value):

SELECT * FROM Sales.Orders WHERE OrderStatus = 'Completed' OR (OrderStatus = 'Pending' AND Amount > 500);

Practice

Find customers from USA with Score > 50 OR from India with Score > 80.

⭐ SECTION 2 — JOINS (Topics 11–20)

Joins combine rows from multiple tables based on related columns.

This is the backbone of analytics, reporting, and ETL.

11. INNER JOIN — match rows in both tables

What it is

Returns rows where the join condition matches in both tables.

Syntax

SELECT ... FROM A INNER JOIN B ON A.key = B.key;

Example

List orders with customer names:

SELECT o.OrderID, o.Amount, c.FirstName, c.LastName FROM Sales.Orders o INNER JOIN Sales.Customers c ON o.CustomerID = c.CustomerID;

Practice

Show OrderID, OrderDate, FirstName for all matching orders.

12. LEFT JOIN — keep all customers, even without orders

What it is

Returns all rows from the left table, and matching rows from the right.

Example

Customers with their orders (including those with none):

SELECT c.CustomerID, c.FirstName, o.OrderID, o.Amount FROM Sales.Customers c LEFT JOIN Sales.Orders o ON c.CustomerID = o.CustomerID;

Practice

Find customers who have no orders.

13. RIGHT JOIN — keep all orders, even without customers
(Less common, but useful)

Example

Orders with customer info (even if customer missing):

SELECT o.OrderID, o.Amount, c.FirstName FROM Sales.Orders o RIGHT JOIN Sales.Customers c ON o.CustomerID = c.CustomerID;

Practice

Show all orders and customer names, even if customer is missing.

14. FULL OUTER JOIN — keep everything

What it is

Returns all rows from both tables.

Example

Customers and orders, even unmatched:

SELECT c.CustomerID, o.OrderID, c.FirstName, o.Amount FROM Sales.Customers c FULL OUTER JOIN Sales.Orders o ON c.CustomerID = o.CustomerID;

Practice

Find all customers and orders, including unmatched.

15. JOIN with additional filters

Example

Completed orders with customer names:

SELECT o.OrderID, o.Amount, c.FirstName FROM Sales.Orders o JOIN Sales.Customers c ON o.CustomerID = c.CustomerIDWHERE o.OrderStatus = 'Completed';

Practice

Find all Pending orders with customer names.

16. JOIN using expressions

Example

Orders where customer Score > 80:

SELECT o.OrderID, o.Amount, c.Score

FROM Sales.Orders o

JOIN Sales.Customers c

ON o.CustomerID = c.CustomerID

WHERE c.Score > 80;

Practice

Find orders where Quantity × Sales > 1000.




17. JOIN + GROUP BY

Example

Total sales per customer:







SELECT c.CustomerID, c.FirstName,

SUM(o.Amount) AS TotalSpent

FROM Sales.Customers c

LEFT JOIN Sales.Orders o

ON c.CustomerID = o.CustomerID

GROUP BY c.CustomerID, c.FirstName;

Practice

Count orders per customer.




18. JOIN with multiple tables

Example

Orders with customer and salesperson:







SELECT o.OrderID, c.FirstName, o.SalesPersonID

FROM Sales.Orders o

JOIN Sales.Customers c

ON o.CustomerID = c.CustomerID;

(You can later add a SalesPerson table.)




Practice

Show OrderID, CustomerName, and SalesPersonID.




19. Anti‑join (LEFT JOIN + NULL)

What it is

Find rows in one table with no match in another.




Example

Customers with no orders:







SELECT c.*

FROM Sales.Customers c

LEFT JOIN Sales.Orders o

ON c.CustomerID = o.CustomerID

WHERE o.OrderID IS NULL;

Practice

Find orders with no matching customer.




20. Semi‑join (EXISTS)

What it is

Check if a match exists, but don’t return the joined rows.




Example

Customers who have placed at least one order:







SELECT *

FROM Sales.Customers c

WHERE EXISTS (

SELECT 1

FROM Sales.Orders o

WHERE o.CustomerID = c.CustomerID

);

Practice

Find customers who placed orders after 2024.







SECTION 3 — Advanced joins (Topics 21–30)

We’ll keep using only your real tables:




Sales.Customers




Sales.Orders




21. Customers with orders in a specific month

Idea: Use EXTRACT‑like logic via MONTH() and YEAR() in Server.




Example — customers with orders in June 2023







SELECT DISTINCT c.CustomerID, c.FirstName, c.LastName

FROM Sales.Customers c

JOIN Sales.Orders o

ON c.CustomerID = o.CustomerID

WHERE YEAR(o.OrderDate) = 2023

AND MONTH(o.OrderDate) = 6;

Practice: Get customers who ordered in January 2024.




22. Customers with orders in multiple months

Idea: Group by customer and count distinct months.




Example







SELECT c.CustomerID, c.FirstName, c.LastName,

COUNT(DISTINCT (CONVERT(char(7), o.OrderDate, 120))) AS DistinctMonths

FROM Sales.Customers c

JOIN Sales.Orders o

ON c.CustomerID = o.CustomerID

GROUP BY c.CustomerID, c.FirstName, c.LastName

HAVING COUNT(DISTINCT (CONVERT(char(7), o.OrderDate, 120))) > 1;

Practice: Find customers who ordered in at least 3 different months.




23. Customers whose first order had high Amount

Idea: Use window functions to find the first order per customer.




Example — first order Amount > 200







WITH OrderedOrders AS (

SELECT o.*,

ROW_NUMBER() OVER (

PARTITION BY o.CustomerID

ORDER BY o.OrderDate

) AS rn

FROM Sales.Orders o

)

SELECT c.CustomerID, c.FirstName, c.LastName

FROM Sales.Customers c

JOIN OrderedOrders o

ON c.CustomerID = o.CustomerID

WHERE o.rn = 1

AND o.Amount > 200;

Practice: Find customers whose first order status was Cancelled.




24. Customers whose last order was Cancelled

Idea: Same pattern, but order by date descending.




Example







WITH OrderedOrders AS (

SELECT o.*,

ROW_NUMBER() OVER (

PARTITION BY o.CustomerID

ORDER BY o.OrderDate DESC

) AS rn

FROM Sales.Orders o

)

SELECT c.CustomerID, c.FirstName, c.LastName

FROM Sales.Customers c

JOIN OrderedOrders o

ON c.CustomerID = o.CustomerID

WHERE o.rn = 1

AND o.OrderStatus = 'Cancelled';

Practice: Find customers whose last order was Completed.




25. Customers with more than one PaymentMethod

Idea: Count distinct payment methods per customer.




Example







SELECT c.CustomerID, c.FirstName, c.LastName,

COUNT(DISTINCT o.PaymentMethod) AS DistinctPaymentMethods

FROM Sales.Customers c

JOIN Sales.Orders o

ON c.CustomerID = o.CustomerID

GROUP BY c.CustomerID, c.FirstName, c.LastName

HAVING COUNT(DISTINCT o.PaymentMethod) > 1;

Practice: Find customers who used exactly one payment method.




26. Customers with both Completed and Pending orders

Idea: Use conditional aggregation.




Example







SELECT c.CustomerID, c.FirstName, c.LastName

FROM Sales.Customers c

JOIN Sales.Orders o

ON c.CustomerID = o.CustomerID

GROUP BY c.CustomerID, c.FirstName, c.LastName

HAVING SUM(CASE WHEN o.OrderStatus = 'Completed' THEN 1 ELSE 0 END) > 0

AND SUM(CASE WHEN o.OrderStatus = 'Pending' THEN 1 ELSE 0 END) > 0;

Practice: Find customers who have both Completed and Cancelled orders.




27. Customers whose order Amount increased over time

Idea: Use LAG to compare each order to the previous one.




Example







WITH Lagged AS (

SELECT o.*,

LAG(o.Amount) OVER (

PARTITION BY o.CustomerID

ORDER BY o.OrderDate

) AS PrevAmount

FROM Sales.Orders o

)

SELECT DISTINCT CustomerID

FROM Lagged

WHERE PrevAmount IS NOT NULL

AND Amount > PrevAmount;

Practice: Find customers whose Amount decreased compared to the previous order.




28. Customers whose order Amount decreased over time

Example







WITH Lagged AS (

SELECT o.*,

LAG(o.Amount) OVER (

PARTITION BY o.CustomerID

ORDER BY o.OrderDate

) AS PrevAmount

FROM Sales.Orders o

)

SELECT DISTINCT CustomerID

FROM Lagged

WHERE PrevAmount IS NOT NULL

AND Amount < PrevAmount;

Practice: Find customers whose Amount stayed the same as previous order.




29. Orders updated on the same day they were created

Idea: Compare CreationTime and UpdatedAt dates.




Example







SELECT *

FROM Sales.Orders

WHERE CONVERT(date, CreationTime) = CONVERT(date, UpdatedAt);

Practice: Find orders updated at least 3 days after CreationTime.




30. Customers with orders in 2023 but not in 2024

Idea: Use two sets and anti‑join logic.




Example







WITH Orders2023 AS (

SELECT DISTINCT CustomerID

FROM Sales.Orders

WHERE YEAR(OrderDate) = 2023

),

Orders2024 AS (

SELECT DISTINCT CustomerID

FROM Sales.Orders

WHERE YEAR(OrderDate) = 2024

)

SELECT c.CustomerID, c.FirstName, c.LastName

FROM Sales.Customers c

JOIN Orders2023 y23

ON c.CustomerID = y23.CustomerID

LEFT JOIN Orders2024 y24

ON c.CustomerID = y24.CustomerID

WHERE y24.CustomerID IS NULL;

Practice: Find customers who ordered in 2024 but not in 2023.







✅ SECTION 4 — Set Operators (31–35)

31. UNION

Combine rows from two queries.







SELECT Country FROM Sales.Customers

UNION

SELECT PaymentMethod FROM Sales.Orders;

32. INTERSECT

Common rows.







SELECT CustomerID FROM Sales.Customers

INTERSECT

SELECT CustomerID FROM Sales.Orders;

33. EXCEPT

Rows in first query but not second.







SELECT CustomerID FROM Sales.Customers

EXCEPT

SELECT CustomerID FROM Sales.Orders;

34. UNION ALL

Keeps duplicates.







SELECT Country FROM Sales.Customers

UNION ALL

SELECT Country FROM Sales.Customers;

35. EXCEPT for date ranges




SELECT CustomerID FROM Sales.Orders WHERE YEAR(OrderDate)=2023

EXCEPT

SELECT CustomerID FROM Sales.Orders WHERE YEAR(OrderDate)=2024;

✅ SECTION 5 — Functions (36–45)

36. ROUND




SELECT OrderID, ROUND(Amount,2) FROM Sales.Orders;

37. Math expression




SELECT OrderID, Amount * 1.10 AS WithTax FROM Sales.Orders;

38. CONCAT




SELECT CONCAT(FirstName,' ',LastName) AS FullName FROM Sales.Customers;

39. SUBSTRING




SELECT SUBSTRING(Email, CHARINDEX('@',Email)+1, 100) AS Domain

FROM Sales.Customers;

40. REPLACE




SELECT REPLACE(ShipAddress,'Street','St') FROM Sales.Orders;

41. UPPER/LOWER




SELECT UPPER(FirstName) FROM Sales.Customers;

42. LEN




SELECT Email FROM Sales.Customers WHERE LEN(Email)>15;

43. TRIM




SELECT LTRIM(RTRIM(Phone)) FROM Sales.Customers;

44. ABS




SELECT ABS(Sales - Amount) FROM Sales.Orders;

45. CEILING/FLOOR




SELECT CEILING(Amount), FLOOR(Amount) FROM Sales.Orders;

✅ SECTION 6 — Date Functions (46–55)

46. YEAR/MONTH/DAY




SELECT YEAR(OrderDate), MONTH(OrderDate) FROM Sales.Orders;

47. DATEADD




SELECT DATEADD(DAY,7,OrderDate) FROM Sales.Orders;

48. DATEDIFF




SELECT DATEDIFF(DAY,OrderDate,ShipDate) FROM Sales.Orders;

49. GETDATE




SELECT * FROM Sales.Customers WHERE CreatedAt > DATEADD(DAY,-30,GETDATE());

50. Orders on weekends




SELECT * FROM Sales.Orders WHERE DATEPART(WEEKDAY,OrderDate) IN (1,7);

51. NULLIF




SELECT NULLIF(Amount,0) FROM Sales.Orders;

52. COALESCE




SELECT COALESCE(Phone,'Unknown') FROM Sales.Customers;

53. ISNULL




SELECT ISNULL(Amount,0) FROM Sales.Orders;

54. CAST/CONVERT




SELECT CONVERT(varchar,OrderDate,23) FROM Sales.Orders;

55. DATEFROMPARTS




SELECT DATEFROMPARTS(2024,1,1);

✅ SECTION 7 — CASE (56–65)

56. Categorize Amount




SELECT CASE WHEN Amount>500 THEN 'High' ELSE 'Low' END FROM Sales.Orders;

57. Categorize Score




SELECT CASE WHEN Score>=80 THEN 'Gold' ELSE 'Regular' END FROM Sales.Customers;

58. Categorize OrderStatus




SELECT CASE OrderStatus WHEN 'Completed' THEN 'Done' ELSE 'Pending' END FROM Sales.Orders;

59. Categorize by Country




SELECT CASE WHEN Country='USA' THEN 'North America' ELSE 'Other' END FROM Sales.Customers;

60. Categorize by Quantity




SELECT CASE WHEN Quantity>10 THEN 'Bulk' ELSE 'Normal' END FROM Sales.Orders;

61–65 similar patterns

(You now know the structure.)




✅ SECTION 8 — Aggregates (66–75)

66. COUNT




SELECT COUNT(*) FROM Sales.Customers;

67. SUM




SELECT SUM(Amount) FROM Sales.Orders;

68. AVG




SELECT AVG(Amount) FROM Sales.Orders;

69. MIN/MAX




SELECT MIN(Amount), MAX(Amount) FROM Sales.Orders;

70. GROUP BY Country




SELECT Country, COUNT(*) FROM Sales.Customers GROUP BY Country;

71. Orders per customer




SELECT CustomerID, COUNT(*) FROM Sales.Orders GROUP BY CustomerID;

72. Revenue per customer




SELECT CustomerID, SUM(Amount) FROM Sales.Orders GROUP BY CustomerID;

73. Orders per status




SELECT OrderStatus, COUNT(*) FROM Sales.Orders GROUP BY OrderStatus;

74. Average Quantity




SELECT AVG(Quantity) FROM Sales.Orders;

75. Monthly orders




SELECT YEAR(OrderDate), MONTH(OrderDate), COUNT(*)

FROM Sales.Orders

GROUP BY YEAR(OrderDate), MONTH(OrderDate);

✅ SECTION 9 — Window Functions (76–95)

76. Running total




SELECT OrderID, Amount,

SUM(Amount) OVER (ORDER BY OrderDate) AS RunningTotal

FROM Sales.Orders;

77. Total per customer




SELECT OrderID, CustomerID,

SUM(Amount) OVER (PARTITION BY CustomerID)

FROM Sales.Orders;

78. Avg per customer




SELECT OrderID, AVG(Amount) OVER (PARTITION BY CustomerID)

FROM Sales.Orders;

79. Count per customer




SELECT OrderID, COUNT(*) OVER (PARTITION BY CustomerID)

FROM Sales.Orders;

80. Percent of total




SELECT OrderID, Amount,

Amount * 1.0 / SUM(Amount) OVER ()

FROM Sales.Orders;

81. Max per customer




SELECT OrderID, MAX(Amount) OVER (PARTITION BY CustomerID)

FROM Sales.Orders;

82. Min per customer




SELECT OrderID, MIN(Amount) OVER (PARTITION BY CustomerID)

FROM Sales.Orders;

83. First order amount




SELECT OrderID,

FIRST_VALUE(Amount) OVER (PARTITION BY CustomerID ORDER BY OrderDate)

FROM Sales.Orders;

84. Last order amount




SELECT OrderID,

LAST_VALUE(Amount) OVER (

PARTITION BY CustomerID ORDER BY OrderDate

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

)

FROM Sales.Orders;

85. LAG




SELECT OrderID, Amount,

LAG(Amount) OVER (PARTITION BY CustomerID ORDER BY OrderDate)

FROM Sales.Orders;

86. LEAD




SELECT OrderID, Amount,

LEAD(Amount) OVER (PARTITION BY CustomerID ORDER BY OrderDate)

FROM Sales.Orders;

87. ROW_NUMBER




SELECT OrderID,

ROW_NUMBER() OVER (ORDER BY Amount DESC)

FROM Sales.Orders;

88. RANK




SELECT OrderID,

RANK() OVER (ORDER BY Amount DESC)

FROM Sales.Orders;

89. DENSE_RANK




SELECT OrderID,

DENSE_RANK() OVER (ORDER BY Amount DESC)

FROM Sales.Orders;

90–95 similar patterns

(Top N per customer, differences, etc.)




✅ SECTION 10 — Subqueries & CTEs (96–105)

96. Subquery filter




SELECT * FROM Sales.Customers

WHERE CustomerID IN (SELECT CustomerID FROM Sales.Orders WHERE Amount>500);

97. Total spend > 1000




SELECT * FROM Sales.Customers

WHERE CustomerID IN (

SELECT CustomerID FROM Sales.Orders GROUP BY CustomerID HAVING SUM(Amount)>1000

);

98. More than 3 orders




SELECT CustomerID FROM Sales.Orders

GROUP BY CustomerID HAVING COUNT(*)>3;

99. First order cancelled




WITH x AS (

SELECT *, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate) rn

FROM Sales.Orders

)

SELECT * FROM x WHERE rn=1 AND OrderStatus='Cancelled';

100. Only ordered in 2023




WITH y AS (

SELECT CustomerID, YEAR(OrderDate) yr FROM Sales.Orders GROUP BY CustomerID,YEAR(OrderDate)

)

SELECT CustomerID FROM y GROUP BY CustomerID HAVING COUNT(*)=1 AND MIN(yr)=2023;

101–105 CTE patterns

(monthly revenue, repeat customers, etc.)




✅ SECTION 11 — Views, CTAS, Temp Tables (106–115)

106. View




CREATE VIEW Sales.CustomerSummary AS

SELECT c.CustomerID, c.FirstName, COUNT(o.OrderID) AS TotalOrders

FROM Sales.Customers c

LEFT JOIN Sales.Orders o ON c.CustomerID=o.CustomerID

GROUP BY c.CustomerID, c.FirstName;

107. High value orders




CREATE VIEW Sales.HighValueOrders AS

SELECT * FROM Sales.Orders WHERE Amount>500;

108. Monthly revenue




CREATE VIEW Sales.MonthlyRevenue AS

SELECT YEAR(OrderDate) yr, MONTH(OrderDate) mn, SUM(Amount) total

FROM Sales.Orders GROUP BY YEAR(OrderDate),MONTH(OrderDate);

109. CTAS




SELECT * INTO Sales.TempHighOrders

FROM Sales.Orders WHERE Amount>500;

110–115 similar patterns

(temp tables, CTAS, etc.)




✅ SECTION 12 — Stored Procedures (116–120)

116. Update order status




CREATE PROCEDURE Sales.UpdateStatus @OrderID int, @Status varchar(50)

AS

UPDATE Sales.Orders SET OrderStatus=@Status WHERE OrderID=@OrderID;

117. Delete cancelled




CREATE PROCEDURE Sales.DeleteCancelled AS

DELETE FROM Sales.Orders WHERE OrderStatus='Cancelled';

118–120 similar patterns

(insert customer, archive orders, etc.)




✅ SECTION 13 — Triggers (121–125)

121. Auto-update UpdatedAt




CREATE TRIGGER trg_UpdateTime ON Sales.Orders

AFTER UPDATE AS

UPDATE o SET UpdatedAt=GETDATE()

FROM Sales.Orders o

JOIN inserted i ON o.OrderID=i.OrderID;

122. Log deletes




CREATE TRIGGER trg_LogDelete ON Sales.Orders

AFTER DELETE AS

INSERT INTO Sales.OrderDeleteLog(OrderID,Amount,DeletedAt)

SELECT OrderID,Amount,GETDATE() FROM deleted;

123–125 similar patterns

(prevent negative, track status changes, etc.)




✅ SECTION 14 — Indexes & Execution Plans (126–135)

126. Index




CREATE INDEX idx_orders_customer ON Sales.Orders(CustomerID);

127. Index on Email




CREATE INDEX idx_customers_email ON Sales.Customers(Email);

128. Composite index




CREATE INDEX idx_orders_customer_date ON Sales.Orders(CustomerID,OrderDate);

129–135

Use:







SET STATISTICS IO ON;

SET STATISTICS TIME ON;

EXPLAIN...

✅ SECTION 15 — Partitioning & Performance (136–150)

Server partitioning example:




136. Create partition function




CREATE PARTITION FUNCTION pfOrders (date)

AS RANGE RIGHT FOR VALUES ('2023-01-01','2024-01-01');

137. Create partition scheme




CREATE PARTITION SCHEME psOrders AS PARTITION pfOrders ALL TO ([PRIMARY]);

138. Create partitioned table




CREATE TABLE Sales.OrdersPartitioned (...)

ON psOrders(OrderDate);

139–150

Performance rewrites:




Replace OR with IN




Use EXISTS instead of IN




Avoid SELECT *




Use covering indexes




Use window functions instead of correlated subqueries




✅ SECTION 16 — AI + (151–155)

151. Convert English →

You already saw many examples.




152. Explain in English

You can ask me anytime.




153. Optimize

Rewrite slow queries using:




indexes




window functions




CTEs




predicate pushdown




154. Generate test data

Use loops or random functions.




155. Convert business question →

Example:




“Top 5 customers by revenue”







SELECT TOP 5 CustomerID, SUM(Amount) AS TotalSpent

FROM Sales.Orders

GROUP BY CustomerID

ORDER BY TotalSpent DESC;

How to Master SQL Using a Single Table: 150+ Real‑World Scenarios

✅ SECTION 1 — Filtering Data (1–10)

You learned how to filter rows using:

Basic WHERE
AND
OR
BETWEEN
IN
LIKE
IS NULL / IS NOT NULL
NOT conditions
Expressions in WHERE
Combined filters with parentheses
All examples used your columns like Country, OrderDate, Amount, Score, etc.

✅ SECTION 2 — Joins (11–20)

You learned:

INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL OUTER JOIN
JOIN with filters
JOIN with expressions
JOIN + GROUP BY
Multi‑table joins
Anti‑join (LEFT JOIN + NULL)
Semi‑join (EXISTS)

All using your real keys: CustomerID, OrderID.

✅ SECTION 3 — Advanced Joins (21–30)

You learned:

Customers with orders in a specific month
Customers ordering in multiple months
First order per customer
Last order per customer
Customers with multiple payment methods
Customers with both Completed & Pending orders
Increasing order amounts
Decreasing order amounts
Orders updated same day
Customers ordering in 2023 but not 2024
All using window functions + joins.

✅ SECTION 4 — Set Operators (31–35)

You learned:

UNION
INTERSECT
EXCEPT
UNION ALL
EXCEPT for date ranges

✅ SECTION 5 — Functions (36–45)

You learned:

ROUND
Math expressions
CONCAT
SUBSTRING
REPLACE
UPPER / LOWER
LEN
TRIM
ABS
CEILING / FLOOR

All applied to your columns like Email, ShipAddress, Amount.

✅ SECTION 6 — Date & NULL Functions (46–55)

You learned:

YEAR / MONTH / DAY
DATEADD
DATEDIFF
GETDATE
Weekend detection
NULLIF
COALESCE
ISNULL
CAST / CONVERT
DATEFROMPARTS
Using your OrderDate, ShipDate, CreatedAt, UpdatedAt.

✅ SECTION 7 — CASE Expressions (56–65)

You learned:

Categorize Amount
Categorize Score
Categorize OrderStatus
Categorize Country
Categorize Quantity

✅ SECTION 8 — Aggregations (66–75)

You learned:

COUNT
SUM
AVG
MIN / MAX
GROUP BY Country
Orders per customer
Revenue per customer
Orders per status
Average Quantity
Monthly orders

✅ SECTION 9 — Window Functions (76–95)

You learned:

Running total
Total per customer
Avg per customer
Count per customer
Percent of total
Max per customer
Min per customer
First order amount
Last order amount
LAG
LEAD
ROW_NUMBER
RANK
DENSE_RANK
Additional window patterns

All using your real columns like Amount, OrderDate.

✅ SECTION 10 — Subqueries & CTEs (96–105)

You learned:

Subquery filters
Total spend > threshold
Customers with > N orders
First order cancelled
Only ordered in 2023
Monthly revenue CTE
Repeat customers
High‑value customers
Increasing order amounts (CTE + LAG)
Recursive date generator

✅ SECTION 11 — Views, CTAS, Temp Tables (106–115)

You learned:

What a VIEW is + example
High value orders view
Monthly revenue view
CTAS (SELECT INTO)
Temp tables
Additional CTAS/temp table patterns

✅ SECTION 12 — Stored Procedures (116–120)

You learned:

Update order status procedure
Delete cancelled orders procedure
Insert customer procedure
Refresh monthly revenue procedure
Archive old orders procedure

✅ SECTION 13 — Triggers (121–125)

You learned:

Auto‑update UpdatedAt
Log deleted orders
Prevent negative Amount
Default values trigger
Track order status changes

Plus:

✔ What a trigger is
✔ Why triggers matter
✔ Practical steps to test triggers

✅ SECTION 14 — Indexes & Execution Plans (126–135)

You learned:

Index on CustomerID
Index on Email
Composite index
Execution plan basics
Execution plan for joins
Execution plan for windows
Execution plan for subqueries
Execution plan for GROUP BY
Execution plan for ORDER BY
Execution plan comparisons

✅ SECTION 15 — Partitioning & Performance (136–150)

You learned:

Partition function
Partition scheme
Partitioned table
Querying partitions
Comparing performance
Rewrite OR → IN
Rewrite subquery → join
DISTINCT vs GROUP BY
EXISTS vs IN
Avoid SELECT *
Use LIMIT/TOP
Predicate pushdown
Covering indexes
Window functions vs correlated subqueries
Reducing scan size

✅ SECTION 16 — AI + (151–155)

You learned:

Convert English →
Explain in English
Optimize using AI
Generate test data
Convert business question →

Redis Interview Keypoints

Architecture & Execution

✅ 1. Redis executes commands in a single thread “Redis uses an event loop to process all client requests sequentially, ensuring atomicity without locks.”

✅ 2. Redis uses non‑blocking I/O multiplexing “Redis leverages epoll/kqueue/select to handle thousands of concurrent connections efficiently.”

✅ 3. Redis guarantees atomic operations “Because commands are executed sequentially, Redis ensures atomicity without explicit transactions.”

✅ 4. Redis supports multiple data structures “Strings, lists, sets, sorted sets, hashes, streams, bitmaps, and hyperloglogs are natively supported.”

✅ 5. Redis optimizes small collections with encodings “Structures like lists and hashes use ziplist/intset encodings to save memory.”

✅ 6. Redis supports pub/sub messaging “Clients can publish and subscribe to channels for real‑time message distribution.”

✅ 7. Redis supports Lua scripting “Lua scripts run atomically inside Redis, enabling complex operations without race conditions.”

✅ 8. Redis supports transactions with MULTI/EXEC “Commands queued in MULTI are executed atomically when EXEC is called.”

✅ 9. Redis supports pipelines “Clients can batch multiple commands to reduce round‑trip latency.”

✅ 10. Redis supports streams for event data “Streams provide append‑only log structures with consumer groups for processing.”

Persistence

✅ 11. Redis supports RDB snapshots “RDB creates point‑in‑time dumps of the dataset into dump.rdb.”

✅ 12. Redis supports AOF logging “AOF logs every write operation into appendonly.aof for durability.”

✅ 13. Redis supports hybrid persistence “RDB + AOF can be enabled together for balance between speed and durability.”

✅ 14. Redis forks child processes for persistence “On BGSAVE or BGREWRITEAOF, Redis forks a child to handle disk writes.”

✅ 15. Redis supports configurable snapshot intervals “save 900 1 means snapshot every 900 seconds if at least 1 key changed.”

✅ 16. Redis supports appendfsync policies “Options are always, everysec, or no, balancing durability vs performance.”

✅ 17. Redis supports AOF rewrite “BGREWRITEAOF compacts the log by rewriting it in the background.”

✅ 18. Redis supports truncated AOF recovery “aof-load-truncated yes allows Redis to start even if AOF is incomplete.”

✅ 19. Redis supports stop‑writes on persistence error “stop-writes-on-bgsave-error yes prevents data loss if snapshotting fails.”

✅ 20. Redis persistence files are stored in dir “Both RDB and AOF files are written to the configured data directory.”


Replication

✅ 21. Redis supports master‑replica replication “Replicas asynchronously copy data from the master.”

✅ 22. Redis supports partial resynchronization “Replicas can catch up using replication backlog without full resync.”

✅ 23. Redis supports replica promotion “SLAVEOF NO ONE promotes a replica to master.”

✅ 24. Redis supports replica authentication “masterauth config ensures replicas authenticate to the master.”

✅ 25. Redis supports min‑replicas‑to‑write “Writes only succeed if enough replicas acknowledge them.”

✅ 26. Redis supports min‑replicas‑max‑lag “Defines maximum lag allowed before writes are stopped.”

✅ 27. Redis supports diskless replication “repl-diskless-sync yes streams data directly without temporary files.”

✅ 28. Redis supports replication backlog buffer “Backlog stores recent writes for replicas to catch up after disconnects.”

✅ 29. Redis supports chained replication “Replicas can replicate from other replicas, not just the master.”

✅ 30. Redis supports replica read‑only mode “By default, replicas serve read queries but reject writes.”

High Availability (Sentinel & Cluster)

✅ 31. Redis Sentinel monitors masters “Sentinel detects failures and promotes replicas automatically.”

✅ 32. Redis Sentinel reconfigures clients “Clients are updated with new master info after failover.”

✅ 33. Redis Sentinel supports quorum “Failover requires majority agreement among Sentinels.”

✅ 34. Redis Cluster shards data “Cluster divides 16,384 hash slots across masters.”

✅ 35. Redis Cluster supports replicas per master “Each master has replicas for redundancy.”

✅ 36. Redis Cluster supports automatic rebalancing “Slots can be migrated between nodes to balance load.”

✅ 37. Redis Cluster supports resharding “Keys can be moved between slots during scaling.”

✅ 38. Redis Cluster supports gossip protocol “Nodes exchange state info via gossip messages.”

✅ 39. Redis Cluster supports failover “Replicas are promoted if a master fails.”

✅ 40. Redis Cluster requires full slot coverage “By default, cluster stops serving if slots are missing.”

Memory Management

✅ 41. Redis enforces maxmemory limits “maxmemory sets a hard RAM cap for the dataset.”

✅ 42. Redis supports eviction policies “Options include allkeys-lru, volatile-ttl, and noeviction.”

✅ 43. Redis supports maxmemory‑samples “Defines how many keys are sampled for eviction decisions.”

✅ 44. Redis supports memory fragmentation monitoring “INFO memory shows fragmentation ratio for tuning.”

✅ 45. Redis supports lazy freeing “lazyfree-lazy-eviction frees memory asynchronously.”

✅ 46. Redis supports memory allocator tuning “Redis can use jemalloc or libc malloc for memory management.”

✅ 47. Redis supports memory usage command “MEMORY USAGE key shows memory consumed by a key.”

✅ 48. Redis supports memory stats “MEMORY STATS provides allocator and fragmentation details.”

✅ 49. Redis supports memory doctor “MEMORY DOCTOR suggests fixes for memory fragmentation.”

✅ 50. Redis supports eviction notifications “Clients can subscribe to keyspace events for evictions.”



Performance & Monitoring

✅ 51. Redis supports INFO command “Provides runtime stats: memory, clients, persistence, replication, CPU, keyspace.”

✅ 52. Redis supports SLOWLOG “Captures queries slower than threshold for tuning.”

✅ 53. Redis supports latency monitor “Tracks operations exceeding configured latency thresholds.”

✅ 54. Redis supports MONITOR command “Streams all commands in real time for debugging.”

✅ 55. Redis supports CLIENT LIST “Shows connected clients with IP, state, and flags.”

✅ 56. Redis supports CLIENT KILL “Terminates misbehaving or unauthorized client connections.”

✅ 57. Redis supports CONFIG GET/SET “Allows runtime inspection and modification of config parameters.”

✅ 58. Redis supports DEBUG commands “DEBUG SEGFAULT or OBJECT commands help diagnose issues.”

✅ 59. Redis supports command stats “INFO commandstats shows per‑command call counts and latencies.”

✅ 60. Redis supports keyspace stats “INFO keyspace shows keys per DB and TTL distribution.”


Security

✅ 61. Redis supports requirepass “Enforces password authentication for clients.”

✅ 62. Redis supports ACLs (Redis 6+) “ACLs define users, commands, and key patterns allowed.”

✅ 63. Redis supports ACL WHOAMI “Shows which user is currently authenticated.”

✅ 64. Redis supports ACL LIST “Lists all ACL rules configured.”

✅ 65. Redis supports rename‑command “Disables or renames dangerous commands like FLUSHALL.”

✅ 66. Redis supports protected‑mode “Enabled by default, prevents unsafe external access.”

✅ 67. Redis supports TLS encryption “Redis can encrypt client‑server traffic with TLS.”

✅ 68. Redis supports firewall binding “bind restricts Redis to specific IP addresses.”

✅ 69. Redis supports port configuration “Default port is 6379, configurable via port.”

✅ 70. Redis supports AUTH command “Clients authenticate with password or ACL user credentials.”

Advanced Features

✅ 71. Redis supports modules “Modules extend Redis with custom data types and commands.”

✅ 72. Redis supports GEO commands “Stores and queries geospatial data using sorted sets.”

✅ 73. Redis supports BIT operations “Bitmaps allow efficient storage and manipulation of binary data.”

✅ 74. Redis supports HyperLogLog “Provides approximate cardinality estimation with low memory usage.”

✅ 75. Redis supports Bloom filters via modules “Modules like RedisBloom add probabilistic data structures.”

✅ 76. Redis supports JSON via modules “RedisJSON allows storing and querying JSON documents.”

✅ 77. Redis supports graph data via modules “RedisGraph enables graph queries using Cypher syntax.”

✅ 78. Redis supports time series via modules “RedisTimeSeries provides efficient time series storage and queries.”

✅ 79. Redis supports search via modules “RediSearch adds full‑text search and secondary indexing.”

✅ 80. Redis supports AI inference via modules “RedisAI integrates ML models for in‑database inference.”

Operational Practices


✅ 81. Redis supports online configuration changes “CONFIG SET allows parameters to be updated at runtime without restarting the server.”

✅ 82. Redis supports runtime inspection of configs “CONFIG GET retrieves current configuration values for verification.”

✅ 83. Redis supports persistence checks “INFO persistence shows last save time, AOF status, and background save progress.”

✅ 84. Redis supports backup via file copy “Copying dump.rdb or appendonly.aof provides a consistent backup snapshot.”

✅ 85. Redis supports restore by file placement “Placing RDB/AOF files back in the data directory restores the dataset on restart.”

✅ 86. Redis supports keyspace notifications “Clients can subscribe to events like set, expire, or evict for monitoring.”

✅ 87. Redis supports database selection “SELECT <db> switches between logical databases (default is DB 0).”

✅ 88. Redis supports flushing databases “FLUSHDB clears one DB, FLUSHALL clears all DBs — dangerous in production.”

✅ 89. Redis supports migration commands “MIGRATE moves keys between instances atomically with copy or replace options.”

✅ 90. Redis supports renaming keys “RENAME changes a key’s name; RENAMENX only if new name doesn’t exist.”

Advanced Administration

✅ 91. Redis supports cluster resharding tools “redis-cli --cluster reshard moves slots between nodes during scaling.”

✅ 92. Redis supports cluster health checks “redis-cli --cluster check validates slot coverage and node states.”

✅ 93. Redis supports sentinel failover testing “SENTINEL failover <master> forces a manual failover for testing.”

✅ 94. Redis supports sentinel monitoring commands “SENTINEL masters and SENTINEL slaves list monitored nodes.”

✅ 95. Redis supports client pause “CLIENT PAUSE temporarily blocks clients for controlled failover or maintenance.”

✅ 96. Redis supports command renaming for safety “Critical commands can be renamed or disabled to prevent accidental misuse.”

✅ 97. Redis supports eviction statistics “INFO stats shows key eviction counts for monitoring memory pressure.”

✅ 98. Redis supports key expiration checks “TTL key shows remaining time before a key expires.”

✅ 99. Redis supports cluster slot mapping “CLUSTER KEYSLOT key shows which slot a key belongs to.”

✅ 100. Redis supports cluster key migration “CLUSTER GETKEYSINSLOT retrieves keys in a specific slot for migration.”

Redis DBA Daily Commands

1. Service Management

sudo systemctl start redis → Start the Redis service if it’s stopped.
sudo systemctl stop redis → Stop Redis safely (use before maintenance).
sudo systemctl restart redis → Restart Redis after config changes.
systemctl status redis → Check if Redis is running and view recent logs.

2. Connectivity & Basic Ops

redis-cli → Open the Redis command-line interface.
redis-cli ping → Quick health check; returns PONG if Redis is alive.
redis-cli SET mykey "hello" → Store a key/value pair.
redis-cli GET mykey → Retrieve the value of a key.

3. Monitoring & Health

redis-cli INFO → Full server stats (memory, clients, persistence, replication).
redis-cli INFO memory → Focused memory usage report.
redis-cli CLIENT LIST → Show all connected clients (IP, state, etc.).
redis-cli MONITOR → Stream all commands in real time (use carefully in prod).
redis-cli SLOWLOG get 10 → Show the 10 most recent slow queries.

4. Persistence & Backup

redis-cli SAVE → Force an immediate RDB snapshot (blocking).
redis-cli BGSAVE → Trigger background snapshot (non-blocking).
redis-cli INFO persistence → Check persistence status (last save, AOF enabled).

Backup files:
dump.rdb → RDB snapshot file.
appendonly.aof → AOF log file.

5. Memory & Key Management

redis-cli DBSIZE → Count how many keys are in the database.
redis-cli KEYS '*' → List all keys (avoid in large DBs, can block).
redis-cli DEL mykey → Delete a specific key.
redis-cli EXPIRE mykey 60 → Set a 60‑second TTL on a key.

6. Replication & High Availability

redis-cli INFO replication → Show replication role (master/replica) and status.
redis-cli SLAVEOF NO ONE → Promote a replica to master.
redis-cli SLAVEOF <master_ip> <master_port> → Make this node a replica of another.

7. Cluster Administration (if enabled)

redis-cli -c CLUSTER NODES → List all cluster nodes and their roles.
redis-cli -c CLUSTER INFO → Cluster health and slot coverage.
redis-cli --cluster check <ip:port> → Validate cluster configuration.

8. Security & Access

redis-cli -a <password> → Authenticate with password.
redis-cli ACL LIST → Show all ACL rules (Redis 6+).
redis-cli ACL WHOAMI → Show which user you’re logged in as.

Redis Daily Health‑Check Runbook

1. Service Status
systemctl status redis
Confirms Redis service is running and shows recent logs.

2. Connectivity
redis-cli ping
Quick test; should return PONG if Redis is alive.

3. Memory & Resource Usage
redis-cli INFO memory
Check total memory used, peak memory, fragmentation ratio.
Watch for memory close to system limits.

4. Persistence
redis-cli INFO persistence
Verify RDB/AOF status, last save time, and whether background saves are succeeding.

5. Replication / HA
redis-cli INFO replication
Shows if this node is master or replica.
Check replica lag and connected replicas.

6. Clients
redis-cli CLIENT LIST | wc -l
Count connected clients.
Useful to detect spikes or stuck connections.

7. Slow Queries
redis-cli SLOWLOG get 5
Review the last 5 slow queries.
Helps identify performance bottlenecks.

8. Keyspace Stats
redis-cli INFO keyspace
Shows number of keys per database and how many have TTLs.
Useful for monitoring growth and expiration behavior.

9. General Server Info
redis-cli INFO server
Check Redis version, uptime, and role.
Confirms you’re running the expected build.

10. Optional Cluster Checks (if enabled)
redis-cli -c CLUSTER INFO
redis-cli -c CLUSTER NODES
Verify cluster health, slot coverage, and node states.

✅ Daily Routine Summary

Service status → Is Redis running?
Ping test → Is it responsive?
Memory check → Is usage healthy?
Persistence check → Are RDB/AOF saves working?
Replication check → Are replicas in sync?
Client count → Any unusual spikes?
Slow queries → Any performance issues?
Keyspace stats → Growth and TTL behavior.
Server info → Version, uptime, role.
Cluster info → Slot coverage and node health (if clustered).

Kubernetes: Complete Feature Summary for Executive Decision‑Makers

Kubernetes is a full‑scale platform that modernizes how applications are deployed, scaled, secured, and operated. It delivers value across eight major capability areas, each directly tied to business outcomes.

1. Reliability & High Availability

Self‑healing containers

Automatic failover

Rolling updates & instant rollbacks

Health checks (liveness/readiness probes)

Multi‑node clustering

ReplicaSets for redundancy

Business impact: Keeps applications online, reduces outages, and improves customer experience.

2. Scalability & Performance

Horizontal Pod Autoscaling (HPA)

Vertical Pod Autoscaling (VPA)

Cluster Autoscaler

Built‑in load balancing

Resource quotas & limits

Business impact: Handles traffic spikes automatically and optimizes resource usage.

3. Security & Compliance

Role‑Based Access Control (RBAC)

Network Policies

Secrets encryption

Pod Security Standards

Image scanning & signing

Namespace isolation

Audit logging

Business impact: Strengthens security posture and supports compliance requirements.

4. Automation & DevOps Enablement

CI/CD integration

GitOps workflows

Automated deployments & rollbacks

Declarative configuration

Infrastructure as Code (IaC)

Business impact: Accelerates delivery, reduces manual errors, and standardizes operations.

5. Environment Standardization

Namespaces for dev/test/prod

Consistent container images

ConfigMaps & Secrets for environment configs

Multi‑OS container support (CentOS, Ubuntu, Debian, etc.)

Business impact: Eliminates “works on my machine” issues and improves developer productivity.

6. Cost Optimization

Efficient bin‑packing

Autoscaling to reduce idle resources

Spot instance support

Multi‑cloud flexibility

High container density

Business impact: Lowers infrastructure costs and prevents over‑provisioning.

7. Multi‑Cloud & Hybrid Cloud Flexibility

Runs on AWS, Azure, GCP, on‑prem, or hybrid

No vendor lock‑in

Disaster recovery across regions

Edge computing support

Business impact: Future‑proofs the organization and enables global deployments.

8. Observability & Monitoring

Metrics (Prometheus, Metrics Server)

Logging (ELK, Loki)

Tracing (Jaeger, OpenTelemetry)

Dashboards (Grafana, Lens)

Business impact: Improves visibility, speeds up troubleshooting, and supports data‑driven decisions.