๐ Question 69: Find Orders Above Monthly Average
Table: orders (order_id, amount, order_date)
WITH monthly_avg AS (
SELECT
DATE_TRUNC('month', order_date) AS month,
AVG(amount) AS avg_amount
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
o.order_id,
o.amount,
o.order_date
FROM orders o
JOIN monthly_avg m
ON DATE_TRUNC('month', o.order_date) = m.month
WHERE o.amount > m.avg_amount;
๐ Question 70: Calculate Customer Repeat Rate by Month
Table: orders (customer_id, order_date)
WITH customer_orders AS (
SELECT
DATE_TRUNC('month', order_date) AS month,
customer_id,
COUNT() AS order_count
FROM orders
GROUP BY month, customer_id
)
SELECT
month,
ROUND(
100.0 *
COUNT(CASE WHEN order_count > 1 THEN 1 END)
/ COUNT(),
2
) AS repeat_rate
FROM customer_orders
GROUP BY month
ORDER BY month;
๐ฏ Concepts Covered:
โ Window Functions
โ Streak Analysis
โ Customer Segmentation
โ Weekly & Monthly KPIs
โ Revenue Analytics
โ Business Intelligence
โ Advanced Aggregations
โ Real Interview Scenarios
โค๏ธ Double Tap For More
Table: orders (order_id, amount, order_date)
WITH monthly_avg AS (
SELECT
DATE_TRUNC('month', order_date) AS month,
AVG(amount) AS avg_amount
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
o.order_id,
o.amount,
o.order_date
FROM orders o
JOIN monthly_avg m
ON DATE_TRUNC('month', o.order_date) = m.month
WHERE o.amount > m.avg_amount;
๐ Question 70: Calculate Customer Repeat Rate by Month
Table: orders (customer_id, order_date)
WITH customer_orders AS (
SELECT
DATE_TRUNC('month', order_date) AS month,
customer_id,
COUNT() AS order_count
FROM orders
GROUP BY month, customer_id
)
SELECT
month,
ROUND(
100.0 *
COUNT(CASE WHEN order_count > 1 THEN 1 END)
/ COUNT(),
2
) AS repeat_rate
FROM customer_orders
GROUP BY month
ORDER BY month;
๐ฏ Concepts Covered:
โ Window Functions
โ Streak Analysis
โ Customer Segmentation
โ Weekly & Monthly KPIs
โ Revenue Analytics
โ Business Intelligence
โ Advanced Aggregations
โ Real Interview Scenarios
โค๏ธ Double Tap For More
โค5
SQL (Structured Query Language) is a standard programming language used to manage and manipulate relational databases. Here are some key concepts to understand the basics of SQL:
1. Database: A database is a structured collection of data organized in tables, which consist of rows and columns.
2. Table: A table is a collection of related data organized in rows and columns. Each row represents a record, and each column represents a specific attribute or field.
3. Query: A SQL query is a request for data or information from a database. Queries are used to retrieve, insert, update, or delete data in a database.
4. CRUD Operations: CRUD stands for Create, Read, Update, and Delete. These are the basic operations performed on data in a database using SQL:
- Create (INSERT): Adds new records to a table.
- Read (SELECT): Retrieves data from one or more tables.
- Update (UPDATE): Modifies existing records in a table.
- Delete (DELETE): Removes records from a table.
5. Data Types: SQL supports various data types to define the type of data that can be stored in each column of a table, such as integer, text, date, and decimal.
6. Constraints: Constraints are rules enforced on data columns to ensure data integrity and consistency. Common constraints include:
- Primary Key: Uniquely identifies each record in a table.
- Foreign Key: Establishes a relationship between two tables.
- Unique: Ensures that all values in a column are unique.
- Not Null: Specifies that a column cannot contain NULL values.
7. Joins: Joins are used to combine rows from two or more tables based on a related column between them. Common types of joins include INNER JOIN, LEFT JOIN (or LEFT OUTER JOIN), RIGHT JOIN (or RIGHT OUTER JOIN), and FULL JOIN (or FULL OUTER JOIN).
8. Aggregate Functions: SQL provides aggregate functions to perform calculations on sets of values. Common aggregate functions include SUM, AVG, COUNT, MIN, and MAX.
9. Group By: The GROUP BY clause is used to group rows that have the same values into summary rows. It is often used with aggregate functions to perform calculations on grouped data.
10. Order By: The ORDER BY clause is used to sort the result set of a query based on one or more columns in ascending or descending order.
Understanding these basic concepts of SQL will help you write queries to interact with databases effectively. Practice writing SQL queries and experimenting with different commands to become proficient in using SQL for database management and manipulation.
SQL Learning Series: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v/1075
1. Database: A database is a structured collection of data organized in tables, which consist of rows and columns.
2. Table: A table is a collection of related data organized in rows and columns. Each row represents a record, and each column represents a specific attribute or field.
3. Query: A SQL query is a request for data or information from a database. Queries are used to retrieve, insert, update, or delete data in a database.
4. CRUD Operations: CRUD stands for Create, Read, Update, and Delete. These are the basic operations performed on data in a database using SQL:
- Create (INSERT): Adds new records to a table.
- Read (SELECT): Retrieves data from one or more tables.
- Update (UPDATE): Modifies existing records in a table.
- Delete (DELETE): Removes records from a table.
5. Data Types: SQL supports various data types to define the type of data that can be stored in each column of a table, such as integer, text, date, and decimal.
6. Constraints: Constraints are rules enforced on data columns to ensure data integrity and consistency. Common constraints include:
- Primary Key: Uniquely identifies each record in a table.
- Foreign Key: Establishes a relationship between two tables.
- Unique: Ensures that all values in a column are unique.
- Not Null: Specifies that a column cannot contain NULL values.
7. Joins: Joins are used to combine rows from two or more tables based on a related column between them. Common types of joins include INNER JOIN, LEFT JOIN (or LEFT OUTER JOIN), RIGHT JOIN (or RIGHT OUTER JOIN), and FULL JOIN (or FULL OUTER JOIN).
8. Aggregate Functions: SQL provides aggregate functions to perform calculations on sets of values. Common aggregate functions include SUM, AVG, COUNT, MIN, and MAX.
9. Group By: The GROUP BY clause is used to group rows that have the same values into summary rows. It is often used with aggregate functions to perform calculations on grouped data.
10. Order By: The ORDER BY clause is used to sort the result set of a query based on one or more columns in ascending or descending order.
Understanding these basic concepts of SQL will help you write queries to interact with databases effectively. Practice writing SQL queries and experimenting with different commands to become proficient in using SQL for database management and manipulation.
SQL Learning Series: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v/1075
โค6
๐ SQL Scenario-Based Interview Questions with Answers (Part 9)
๐ผ FAANG & Product Company SQL Interview Scenarios
๐ Question 81: Find Users Who Were Active on 3 Consecutive Days
Table: user_activity (user_id, activity_date)
WITH activity AS (
SELECT DISTINCT
user_id,
activity_date
FROM user_activity
),
groups AS (
SELECT
user_id,
activity_date,
activity_date -
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY activity_date
) * INTERVAL '1 day' AS grp
FROM activity
)
SELECT
user_id,
COUNT() AS consecutive_days
FROM groups
GROUP BY user_id, grp
HAVING COUNT() >= 3;
๐ Question 82: Find the Top 5 Selling Products in Each Category
Tables: products (product_id, category) sales (product_id, quantity)
WITH product_sales AS (
SELECT
p.category,
s.product_id,
SUM(s.quantity) AS total_quantity
FROM sales s
JOIN products p
ON s.product_id = p.product_id
GROUP BY p.category, s.product_id
)
SELECT *
FROM (
SELECT *,
DENSE_RANK() OVER (
PARTITION BY category
ORDER BY total_quantity DESC
) AS rnk
FROM product_sales
) t
WHERE rnk <= 5;
๐ Question 83: Find Customers Whose Latest Order Is Their Highest Value Order
Table: orders (customer_id, order_date, amount)
WITH customer_orders AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date DESC
) AS latest_order,
RANK() OVER (
PARTITION BY customer_id
ORDER BY amount DESC
) AS highest_order
FROM orders
)
SELECT
customer_id,
order_date,
amount
FROM customer_orders
WHERE latest_order = 1
AND highest_order = 1;
๐ Question 84: Calculate Rolling 30-Day Revenue
Table: sales (sale_date, amount)
SELECT
sale_date,
SUM(amount) OVER (
ORDER BY sale_date
RANGE BETWEEN INTERVAL '29 days' PRECEDING
AND CURRENT ROW
) AS rolling_30_day_revenue
FROM sales;
๐ Question 85: Find Products Purchased by Exactly One Customer
Table: orders (customer_id, product_id)
SELECT
product_id
FROM orders
GROUP BY product_id
HAVING COUNT(DISTINCT customer_id) = 1;
๐ Question 86: Find the Longest Inactive Period for Each Customer
Table: orders (customer_id, order_date)
WITH gaps AS (
SELECT
customer_id,
order_date,
order_date -
LAG(order_date) OVER (
PARTITION BY customer_id
ORDER BY order_date
) AS inactive_days
FROM orders
)
SELECT
customer_id,
MAX(inactive_days) AS longest_gap
FROM gaps
GROUP BY customer_id;
๐ Question 87: Calculate Revenue Share by Product Category
Tables: products (product_id, category) sales (product_id, amount)
SELECT
category,
SUM(amount) AS revenue,
ROUND(
100.0 * SUM(amount) /
SUM(SUM(amount)) OVER (),
2
) AS revenue_share
FROM sales s
JOIN products p
ON s.product_id = p.product_id
GROUP BY category
ORDER BY revenue DESC;
๐ผ FAANG & Product Company SQL Interview Scenarios
๐ Question 81: Find Users Who Were Active on 3 Consecutive Days
Table: user_activity (user_id, activity_date)
WITH activity AS (
SELECT DISTINCT
user_id,
activity_date
FROM user_activity
),
groups AS (
SELECT
user_id,
activity_date,
activity_date -
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY activity_date
) * INTERVAL '1 day' AS grp
FROM activity
)
SELECT
user_id,
COUNT() AS consecutive_days
FROM groups
GROUP BY user_id, grp
HAVING COUNT() >= 3;
๐ Question 82: Find the Top 5 Selling Products in Each Category
Tables: products (product_id, category) sales (product_id, quantity)
WITH product_sales AS (
SELECT
p.category,
s.product_id,
SUM(s.quantity) AS total_quantity
FROM sales s
JOIN products p
ON s.product_id = p.product_id
GROUP BY p.category, s.product_id
)
SELECT *
FROM (
SELECT *,
DENSE_RANK() OVER (
PARTITION BY category
ORDER BY total_quantity DESC
) AS rnk
FROM product_sales
) t
WHERE rnk <= 5;
๐ Question 83: Find Customers Whose Latest Order Is Their Highest Value Order
Table: orders (customer_id, order_date, amount)
WITH customer_orders AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date DESC
) AS latest_order,
RANK() OVER (
PARTITION BY customer_id
ORDER BY amount DESC
) AS highest_order
FROM orders
)
SELECT
customer_id,
order_date,
amount
FROM customer_orders
WHERE latest_order = 1
AND highest_order = 1;
๐ Question 84: Calculate Rolling 30-Day Revenue
Table: sales (sale_date, amount)
SELECT
sale_date,
SUM(amount) OVER (
ORDER BY sale_date
RANGE BETWEEN INTERVAL '29 days' PRECEDING
AND CURRENT ROW
) AS rolling_30_day_revenue
FROM sales;
๐ Question 85: Find Products Purchased by Exactly One Customer
Table: orders (customer_id, product_id)
SELECT
product_id
FROM orders
GROUP BY product_id
HAVING COUNT(DISTINCT customer_id) = 1;
๐ Question 86: Find the Longest Inactive Period for Each Customer
Table: orders (customer_id, order_date)
WITH gaps AS (
SELECT
customer_id,
order_date,
order_date -
LAG(order_date) OVER (
PARTITION BY customer_id
ORDER BY order_date
) AS inactive_days
FROM orders
)
SELECT
customer_id,
MAX(inactive_days) AS longest_gap
FROM gaps
GROUP BY customer_id;
๐ Question 87: Calculate Revenue Share by Product Category
Tables: products (product_id, category) sales (product_id, amount)
SELECT
category,
SUM(amount) AS revenue,
ROUND(
100.0 * SUM(amount) /
SUM(SUM(amount)) OVER (),
2
) AS revenue_share
FROM sales s
JOIN products p
ON s.product_id = p.product_id
GROUP BY category
ORDER BY revenue DESC;
โค1
๐ Question 88: Find Customers Who Purchased in Every Month of a Year
Table: orders (customer_id, order_date)
SELECT
customer_id
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2025
GROUP BY customer_id
HAVING COUNT(
DISTINCT EXTRACT(MONTH FROM order_date)
) = 12;
๐ Question 89: Find the Most Frequently Bought Product After Product A
Table: order_items (order_id, product_id, sequence_no)
SELECT
b.product_id,
COUNT(*) AS purchase_count
FROM order_items a
JOIN order_items b
ON a.order_id = b.order_id
AND b.sequence_no = a.sequence_no + 1
WHERE a.product_id = 'Product_A'
GROUP BY b.product_id
ORDER BY purchase_count DESC
LIMIT 1;
๐ Question 90: Calculate Customer Lifetime in Days
Tables: customers (customer_id) orders (customer_id, order_date)
SELECT
customer_id,
MAX(order_date) - MIN(order_date) AS lifetime_days
FROM orders
GROUP BY customer_id;
โค๏ธ Double Tap For More
Table: orders (customer_id, order_date)
SELECT
customer_id
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2025
GROUP BY customer_id
HAVING COUNT(
DISTINCT EXTRACT(MONTH FROM order_date)
) = 12;
๐ Question 89: Find the Most Frequently Bought Product After Product A
Table: order_items (order_id, product_id, sequence_no)
SELECT
b.product_id,
COUNT(*) AS purchase_count
FROM order_items a
JOIN order_items b
ON a.order_id = b.order_id
AND b.sequence_no = a.sequence_no + 1
WHERE a.product_id = 'Product_A'
GROUP BY b.product_id
ORDER BY purchase_count DESC
LIMIT 1;
๐ Question 90: Calculate Customer Lifetime in Days
Tables: customers (customer_id) orders (customer_id, order_date)
SELECT
customer_id,
MAX(order_date) - MIN(order_date) AS lifetime_days
FROM orders
GROUP BY customer_id;
โค๏ธ Double Tap For More
โค5
๐ SQL Scenario-Based Interview Questions with Answers Part 10
๐ Question 91: Find the Top 3 Customers Contributing 50% of Total Revenue
Table: orders (customer_id, amount)
WITH customer_revenue AS (
SELECT
customer_id,
SUM(amount) AS revenue
FROM orders
GROUP BY customer_id
),
ranked AS (
SELECT
customer_id,
revenue,
SUM(revenue) OVER (ORDER BY revenue DESC) AS running_revenue,
SUM(revenue) OVER () AS total_revenue
FROM customer_revenue
)
SELECT
customer_id,
revenue
FROM ranked
WHERE running_revenue <= total_revenue * 0.50
LIMIT 3;
๐ Question 92: Find the First Product Purchased by Every Customer
Table: orders (customer_id, product_id, order_date)
WITH ranked_orders AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date
) AS rn
FROM orders
)
SELECT
customer_id,
product_id,
order_date
FROM ranked_orders
WHERE rn = 1;
๐ Question 93: Find Users Who Logged In Every Week for the Last 12 Weeks
Table: logins (user_id, login_date)
SELECT
user_id
FROM logins
WHERE login_date >= CURRENT_DATE - INTERVAL '84 days'
GROUP BY user_id
HAVING COUNT(
DISTINCT DATE_TRUNC('week', login_date)
) = 12;
๐ Question 94: Find the Most Profitable Product
Tables:
products (product_id, cost_price)
sales (product_id, selling_price, quantity)
SELECT
s.product_id,
SUM(
(selling_price - cost_price) * quantity
) AS profit
FROM sales s
JOIN products p
ON s.product_id = p.product_id
GROUP BY s.product_id
ORDER BY profit DESC
LIMIT 1;
๐ Question 95: Find the Longest Continuous Subscription
Table: subscriptions (user_id, start_date, end_date)
SELECT
user_id,
MAX(end_date - start_date) AS subscription_days
FROM subscriptions
GROUP BY user_id
ORDER BY subscription_days DESC
LIMIT 1;
๐ Question 96: Calculate Revenue Lost Due to Returned Orders
Tables:
orders (order_id, amount)
returns (order_id)
SELECT
SUM(o.amount) AS lost_revenue
FROM orders o
JOIN returns r
ON o.order_id = r.order_id;
๐ Question 97: Find Customers Who Bought the Same Product More Than Once
Table: orders (customer_id, product_id)
SELECT
customer_id,
product_id,
COUNT() AS purchase_count
FROM orders
GROUP BY customer_id, product_id
HAVING COUNT() > 1;
๐ Question 98: Find the Peak Sales Month for Every Year
Table: sales (sale_date, amount)
WITH monthly_sales AS (
SELECT
EXTRACT(YEAR FROM sale_date) AS year,
DATE_TRUNC('month', sale_date) AS month,
SUM(amount) AS revenue
FROM sales
GROUP BY
EXTRACT(YEAR FROM sale_date),
DATE_TRUNC('month', sale_date)
)
SELECT
year,
month,
revenue
FROM (
SELECT *,
DENSE_RANK() OVER (
PARTITION BY year
ORDER BY revenue DESC
) AS rnk
FROM monthly_sales
) t
WHERE rnk = 1;
๐ Question 99: Find Customers Who Purchased All Products
Tables:
customers (customer_id)
products (product_id)
orders (customer_id, product_id)
๐ Question 91: Find the Top 3 Customers Contributing 50% of Total Revenue
Table: orders (customer_id, amount)
WITH customer_revenue AS (
SELECT
customer_id,
SUM(amount) AS revenue
FROM orders
GROUP BY customer_id
),
ranked AS (
SELECT
customer_id,
revenue,
SUM(revenue) OVER (ORDER BY revenue DESC) AS running_revenue,
SUM(revenue) OVER () AS total_revenue
FROM customer_revenue
)
SELECT
customer_id,
revenue
FROM ranked
WHERE running_revenue <= total_revenue * 0.50
LIMIT 3;
๐ Question 92: Find the First Product Purchased by Every Customer
Table: orders (customer_id, product_id, order_date)
WITH ranked_orders AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date
) AS rn
FROM orders
)
SELECT
customer_id,
product_id,
order_date
FROM ranked_orders
WHERE rn = 1;
๐ Question 93: Find Users Who Logged In Every Week for the Last 12 Weeks
Table: logins (user_id, login_date)
SELECT
user_id
FROM logins
WHERE login_date >= CURRENT_DATE - INTERVAL '84 days'
GROUP BY user_id
HAVING COUNT(
DISTINCT DATE_TRUNC('week', login_date)
) = 12;
๐ Question 94: Find the Most Profitable Product
Tables:
products (product_id, cost_price)
sales (product_id, selling_price, quantity)
SELECT
s.product_id,
SUM(
(selling_price - cost_price) * quantity
) AS profit
FROM sales s
JOIN products p
ON s.product_id = p.product_id
GROUP BY s.product_id
ORDER BY profit DESC
LIMIT 1;
๐ Question 95: Find the Longest Continuous Subscription
Table: subscriptions (user_id, start_date, end_date)
SELECT
user_id,
MAX(end_date - start_date) AS subscription_days
FROM subscriptions
GROUP BY user_id
ORDER BY subscription_days DESC
LIMIT 1;
๐ Question 96: Calculate Revenue Lost Due to Returned Orders
Tables:
orders (order_id, amount)
returns (order_id)
SELECT
SUM(o.amount) AS lost_revenue
FROM orders o
JOIN returns r
ON o.order_id = r.order_id;
๐ Question 97: Find Customers Who Bought the Same Product More Than Once
Table: orders (customer_id, product_id)
SELECT
customer_id,
product_id,
COUNT() AS purchase_count
FROM orders
GROUP BY customer_id, product_id
HAVING COUNT() > 1;
๐ Question 98: Find the Peak Sales Month for Every Year
Table: sales (sale_date, amount)
WITH monthly_sales AS (
SELECT
EXTRACT(YEAR FROM sale_date) AS year,
DATE_TRUNC('month', sale_date) AS month,
SUM(amount) AS revenue
FROM sales
GROUP BY
EXTRACT(YEAR FROM sale_date),
DATE_TRUNC('month', sale_date)
)
SELECT
year,
month,
revenue
FROM (
SELECT *,
DENSE_RANK() OVER (
PARTITION BY year
ORDER BY revenue DESC
) AS rnk
FROM monthly_sales
) t
WHERE rnk = 1;
๐ Question 99: Find Customers Who Purchased All Products
Tables:
customers (customer_id)
products (product_id)
orders (customer_id, product_id)
โค1
SELECT
customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(DISTINCT product_id) = (
SELECT COUNT(*)
FROM products
);
๐ Question 100: Rank Customers by Lifetime Revenue
Table: orders (customer_id, amount)
WITH customer_revenue AS (
SELECT
customer_id,
SUM(amount) AS lifetime_revenue
FROM orders
GROUP BY customer_id
)
SELECT
customer_id,
lifetime_revenue,
DENSE_RANK() OVER (
ORDER BY lifetime_revenue DESC
) AS revenue_rank
FROM customer_revenue;
๐ก Pro Tip: Practice these regularly, understand the business logic behind each solution, and you'll be well-prepared for SQL interviews at product companies, startups, fintech firms, and MNCs.
โค๏ธ Double Tap For More
customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(DISTINCT product_id) = (
SELECT COUNT(*)
FROM products
);
๐ Question 100: Rank Customers by Lifetime Revenue
Table: orders (customer_id, amount)
WITH customer_revenue AS (
SELECT
customer_id,
SUM(amount) AS lifetime_revenue
FROM orders
GROUP BY customer_id
)
SELECT
customer_id,
lifetime_revenue,
DENSE_RANK() OVER (
ORDER BY lifetime_revenue DESC
) AS revenue_rank
FROM customer_revenue;
๐ก Pro Tip: Practice these regularly, understand the business logic behind each solution, and you'll be well-prepared for SQL interviews at product companies, startups, fintech firms, and MNCs.
โค๏ธ Double Tap For More
โค9
๐ Top 11 SQL Project Ideas to Build a Strong Data Analytics Portfolio
Building projects is one of the fastest ways to improve your SQL skills and stand out in interviews. Here are 11 real-world project ideas:
1๏ธโฃ E-Commerce Sales Analysis
Analyze sales trends
Top-selling products
Customer segmentation
Revenue by category
Repeat customer analysis
2๏ธโฃ Banking Transaction Analysis
Detect fraudulent transactions
Monthly account activity
Customer spending patterns
Balance trends
High-value transactions
3๏ธโฃ Food Delivery Analytics
Delivery time analysis
Restaurant performance
Peak ordering hours
Customer retention
Delivery partner efficiency
4๏ธโฃ HR Analytics Dashboard
Employee attrition
Salary analysis
Department-wise performance
Hiring trends
Attendance insights
5๏ธโฃ Hospital Management Analysis
Patient admissions
Doctor utilization
Readmission rate
Bed occupancy
Treatment costs
6๏ธโฃ Netflix Movie & TV Show Analysis
Most popular genres
Content by country
Ratings analysis
Release trends
Duration analysis
7๏ธโฃ IPL Cricket Data Analysis
Top batsmen
Best bowlers
Team performance
Venue analysis
Winning trends
8๏ธโฃ Retail Inventory Management
Stock availability
Inventory turnover
Slow-moving products
Supplier performance
Stock-out analysis
9๏ธโฃ Ride-Sharing Analytics
Peak ride hours
Driver earnings
Customer retention
Trip cancellation rate
City-wise demand
๐ Finance & Expense Tracker
Monthly expenses
Budget vs actual
Savings analysis
Category-wise spending
Cash flow trends
1๏ธโฃ1๏ธโฃ Social Media Analytics
User engagement
Daily Active Users DAU
Monthly Active Users MAU
Content performance
User retention
๐ฅ Double Tap โค๏ธ For More
Building projects is one of the fastest ways to improve your SQL skills and stand out in interviews. Here are 11 real-world project ideas:
1๏ธโฃ E-Commerce Sales Analysis
Analyze sales trends
Top-selling products
Customer segmentation
Revenue by category
Repeat customer analysis
2๏ธโฃ Banking Transaction Analysis
Detect fraudulent transactions
Monthly account activity
Customer spending patterns
Balance trends
High-value transactions
3๏ธโฃ Food Delivery Analytics
Delivery time analysis
Restaurant performance
Peak ordering hours
Customer retention
Delivery partner efficiency
4๏ธโฃ HR Analytics Dashboard
Employee attrition
Salary analysis
Department-wise performance
Hiring trends
Attendance insights
5๏ธโฃ Hospital Management Analysis
Patient admissions
Doctor utilization
Readmission rate
Bed occupancy
Treatment costs
6๏ธโฃ Netflix Movie & TV Show Analysis
Most popular genres
Content by country
Ratings analysis
Release trends
Duration analysis
7๏ธโฃ IPL Cricket Data Analysis
Top batsmen
Best bowlers
Team performance
Venue analysis
Winning trends
8๏ธโฃ Retail Inventory Management
Stock availability
Inventory turnover
Slow-moving products
Supplier performance
Stock-out analysis
9๏ธโฃ Ride-Sharing Analytics
Peak ride hours
Driver earnings
Customer retention
Trip cancellation rate
City-wise demand
๐ Finance & Expense Tracker
Monthly expenses
Budget vs actual
Savings analysis
Category-wise spending
Cash flow trends
1๏ธโฃ1๏ธโฃ Social Media Analytics
User engagement
Daily Active Users DAU
Monthly Active Users MAU
Content performance
User retention
๐ฅ Double Tap โค๏ธ For More
โค11
Data Analytics Interview Questions with Answers
1. What are Query and Query language?
A query is nothing but a request sent to a database to retrieve data or information. The required data can be retrieved from a table or many tables in the database.
Query languages use various types of queries to retrieve data from databases. SQL, Datalog, and AQL are a few examples of query languages; however, SQL is known to be the widely used query language.
2. What are Superkey and candidate key?
A super key may be a single or a combination of keys that help to identify a record in a table. Know that Super keys can have one or more attributes, even though all the attributes are not necessary to identify the records.
A candidate key is the subset of Superkey, which can have one or more than one attributes to identify records in a table. Unlike Superkey, all the attributes of the candidate key must be helpful to identify the records.
3. What do you mean by buffer pool and mention its benefits?
A buffer pool in SQL is also known as a buffer cache. All the resources can store their cached data pages in a buffer pool. The size of the buffer pool can be defined during the configuration of an instance of SQL Server.
The following are the benefits of a buffer pool:
Increase in I/O performance
Reduction in I/O latency
Increase in transaction throughput
Increase in reading performance
4. What is the difference between Zero and NULL values in SQL?
When a field in a column doesnโt have any value, it is said to be having a NULL value. Simply put, NULL is the blank field in a table. It can be considered as an unassigned, unknown, or unavailable value. On the contrary, zero is a number, and it is an available, assigned, and known value.
1. What are Query and Query language?
A query is nothing but a request sent to a database to retrieve data or information. The required data can be retrieved from a table or many tables in the database.
Query languages use various types of queries to retrieve data from databases. SQL, Datalog, and AQL are a few examples of query languages; however, SQL is known to be the widely used query language.
2. What are Superkey and candidate key?
A super key may be a single or a combination of keys that help to identify a record in a table. Know that Super keys can have one or more attributes, even though all the attributes are not necessary to identify the records.
A candidate key is the subset of Superkey, which can have one or more than one attributes to identify records in a table. Unlike Superkey, all the attributes of the candidate key must be helpful to identify the records.
3. What do you mean by buffer pool and mention its benefits?
A buffer pool in SQL is also known as a buffer cache. All the resources can store their cached data pages in a buffer pool. The size of the buffer pool can be defined during the configuration of an instance of SQL Server.
The following are the benefits of a buffer pool:
Increase in I/O performance
Reduction in I/O latency
Increase in transaction throughput
Increase in reading performance
4. What is the difference between Zero and NULL values in SQL?
When a field in a column doesnโt have any value, it is said to be having a NULL value. Simply put, NULL is the blank field in a table. It can be considered as an unassigned, unknown, or unavailable value. On the contrary, zero is a number, and it is an available, assigned, and known value.
โค7
๐ SQL Project Series #1
E-Commerce Sales Analysis Project ๐
Build a real-world SQL project from scratch and learn the SQL skills required for Data Analyst interviews.
๐ฏ Business Objectives
โ Analyze total sales and revenue
โ Identify top-selling products
โ Find the best-performing product categories
โ Calculate monthly sales trends
โ Identify repeat customers
โ Find inactive customers
โ Calculate Average Order Value (AOV)
โ Calculate Customer Lifetime Value (CLV)
โ Analyze customer purchasing behavior
๐ Step 1: Create Database
CREATE DATABASE ecommerce_db;
USE ecommerce_db;
๐ Step 2: Create Customers Table
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100),
gender VARCHAR(10),
city VARCHAR(50),
signup_date DATE
);
๐ Step 3: Create Products Table
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
category VARCHAR(50),
price DECIMAL(10,2)
);
๐ Step 4: Create Orders Table
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
order_status VARCHAR(30),
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
);
๐ Step 5: Create Order_Items Table
CREATE TABLE order_items (
order_item_id INT PRIMARY KEY,
order_id INT,
product_id INT,
quantity INT,
unit_price DECIMAL(10,2),
FOREIGN KEY (order_id)
REFERENCES orders(order_id),
FOREIGN KEY (product_id)
REFERENCES products(product_id)
);
๐ Step 6: Insert Sample Customers
INSERT INTO customers VALUES
(1,'Rahul','Male','Mumbai','2025-01-10'),
(2,'Priya','Female','Delhi','2025-01-15'),
(3,'Amit','Male','Pune','2025-02-01'),
(4,'Sneha','Female','Bangalore','2025-02-10'),
(5,'Rohan','Male','Hyderabad','2025-03-05');
๐ Step 7: Insert Sample Products
INSERT INTO products VALUES
(101,'Laptop','Electronics',65000),
(102,'Headphones','Electronics',2500),
(103,'Office Chair','Furniture',7000),
(104,'Keyboard','Electronics',1800),
(105,'Water Bottle','Home',600);
๐ Step 8: Insert Sample Orders
INSERT INTO orders VALUES
(1001,1,'2025-03-01','Delivered'),
(1002,2,'2025-03-03','Delivered'),
(1003,1,'2025-03-10','Delivered'),
(1004,3,'2025-03-15','Cancelled'),
(1005,4,'2025-03-20','Delivered');
๐ Step 9: Insert Sample Order Items
INSERT INTO order_items VALUES
(1,1001,101,1,65000),
(2,1001,102,2,2500),
(3,1002,103,1,7000),
(4,1003,104,1,1800),
(5,1004,105,3,600),
(6,1005,101,1,65000);
๐ง SQL Concepts You'll Practice
โ DDL Commands
โ DML Commands
โ Primary & Foreign Keys
โ Joins
โ Aggregate Functions
โ GROUP BY
โ HAVING
โ CASE WHEN
โ Subqueries
โ CTEs
โ Window Functions
โ Date Functions
๐ Business KPIs You Can Build
๐ Total Revenue
๐ Total Orders
๐ Total Customers
๐ Average Order Value (AOV)
๐ Revenue by Product Category
๐ Monthly Sales Trend
๐ Daily Sales Trend
๐ Top 10 Selling Products
๐ Top 10 Customers by Revenue
๐ Revenue by City
๐ Revenue by Gender
๐ Customer Lifetime Value (CLV)
๐ Repeat Purchase Rate
๐ Customer Retention Rate
๐ Customer Churn Rate
๐ Average Products per Order
๐ Order Cancellation Rate
๐ Delivered vs Cancelled Orders
๐ Best Selling Category
๐ Worst Selling Category
๐ Most Expensive Product Sold
๐ Highest Revenue Month
๐ Customer Acquisition by Month
๐ New vs Returning Customers
๐ Product-wise Revenue
๐ Category-wise Revenue Contribution
๐ฏ Double Tap โค๏ธ For Part-2
E-Commerce Sales Analysis Project ๐
Build a real-world SQL project from scratch and learn the SQL skills required for Data Analyst interviews.
๐ฏ Business Objectives
โ Analyze total sales and revenue
โ Identify top-selling products
โ Find the best-performing product categories
โ Calculate monthly sales trends
โ Identify repeat customers
โ Find inactive customers
โ Calculate Average Order Value (AOV)
โ Calculate Customer Lifetime Value (CLV)
โ Analyze customer purchasing behavior
๐ Step 1: Create Database
CREATE DATABASE ecommerce_db;
USE ecommerce_db;
๐ Step 2: Create Customers Table
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100),
gender VARCHAR(10),
city VARCHAR(50),
signup_date DATE
);
๐ Step 3: Create Products Table
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
category VARCHAR(50),
price DECIMAL(10,2)
);
๐ Step 4: Create Orders Table
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
order_status VARCHAR(30),
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
);
๐ Step 5: Create Order_Items Table
CREATE TABLE order_items (
order_item_id INT PRIMARY KEY,
order_id INT,
product_id INT,
quantity INT,
unit_price DECIMAL(10,2),
FOREIGN KEY (order_id)
REFERENCES orders(order_id),
FOREIGN KEY (product_id)
REFERENCES products(product_id)
);
๐ Step 6: Insert Sample Customers
INSERT INTO customers VALUES
(1,'Rahul','Male','Mumbai','2025-01-10'),
(2,'Priya','Female','Delhi','2025-01-15'),
(3,'Amit','Male','Pune','2025-02-01'),
(4,'Sneha','Female','Bangalore','2025-02-10'),
(5,'Rohan','Male','Hyderabad','2025-03-05');
๐ Step 7: Insert Sample Products
INSERT INTO products VALUES
(101,'Laptop','Electronics',65000),
(102,'Headphones','Electronics',2500),
(103,'Office Chair','Furniture',7000),
(104,'Keyboard','Electronics',1800),
(105,'Water Bottle','Home',600);
๐ Step 8: Insert Sample Orders
INSERT INTO orders VALUES
(1001,1,'2025-03-01','Delivered'),
(1002,2,'2025-03-03','Delivered'),
(1003,1,'2025-03-10','Delivered'),
(1004,3,'2025-03-15','Cancelled'),
(1005,4,'2025-03-20','Delivered');
๐ Step 9: Insert Sample Order Items
INSERT INTO order_items VALUES
(1,1001,101,1,65000),
(2,1001,102,2,2500),
(3,1002,103,1,7000),
(4,1003,104,1,1800),
(5,1004,105,3,600),
(6,1005,101,1,65000);
๐ง SQL Concepts You'll Practice
โ DDL Commands
โ DML Commands
โ Primary & Foreign Keys
โ Joins
โ Aggregate Functions
โ GROUP BY
โ HAVING
โ CASE WHEN
โ Subqueries
โ CTEs
โ Window Functions
โ Date Functions
๐ Business KPIs You Can Build
๐ Total Revenue
๐ Total Orders
๐ Total Customers
๐ Average Order Value (AOV)
๐ Revenue by Product Category
๐ Monthly Sales Trend
๐ Daily Sales Trend
๐ Top 10 Selling Products
๐ Top 10 Customers by Revenue
๐ Revenue by City
๐ Revenue by Gender
๐ Customer Lifetime Value (CLV)
๐ Repeat Purchase Rate
๐ Customer Retention Rate
๐ Customer Churn Rate
๐ Average Products per Order
๐ Order Cancellation Rate
๐ Delivered vs Cancelled Orders
๐ Best Selling Category
๐ Worst Selling Category
๐ Most Expensive Product Sold
๐ Highest Revenue Month
๐ Customer Acquisition by Month
๐ New vs Returning Customers
๐ Product-wise Revenue
๐ Category-wise Revenue Contribution
๐ฏ Double Tap โค๏ธ For Part-2
โค22๐1
1. What is the difference between SQL and MySQL?
SQL is a standard language for retrieving and manipulating structured databases. On the contrary, MySQL is a relational database management system, like SQL Server, Oracle or IBM DB2, that is used to manage SQL databases.
2. What is a Cross-Join?
Cross join can be defined as a cartesian product of the two tables included in the join. The table after join contains the same number of rows as in the cross-product of the number of rows in the two tables. If a WHERE clause is used in cross join then the query will work like an INNER JOIN.
3. What is a Stored Procedure?
A stored procedure is a subroutine available to applications that access a relational database management system (RDBMS). Such procedures are stored in the database data dictionary. The sole disadvantage of stored procedure is that it can be executed nowhere except in the database and occupies more memory in the database server.
4. What is Pattern Matching in SQL?
SQL pattern matching provides for pattern search in data if you have no clue as to what that word should be. This kind of SQL query uses wildcards to match a string pattern, rather than writing the exact word. The LIKE operator is used in conjunction with SQL Wildcards to fetch the required information.
SQL is a standard language for retrieving and manipulating structured databases. On the contrary, MySQL is a relational database management system, like SQL Server, Oracle or IBM DB2, that is used to manage SQL databases.
2. What is a Cross-Join?
Cross join can be defined as a cartesian product of the two tables included in the join. The table after join contains the same number of rows as in the cross-product of the number of rows in the two tables. If a WHERE clause is used in cross join then the query will work like an INNER JOIN.
3. What is a Stored Procedure?
A stored procedure is a subroutine available to applications that access a relational database management system (RDBMS). Such procedures are stored in the database data dictionary. The sole disadvantage of stored procedure is that it can be executed nowhere except in the database and occupies more memory in the database server.
4. What is Pattern Matching in SQL?
SQL pattern matching provides for pattern search in data if you have no clue as to what that word should be. This kind of SQL query uses wildcards to match a string pattern, rather than writing the exact word. The LIKE operator is used in conjunction with SQL Wildcards to fetch the required information.
โค4๐4
๐ SQL Project Series #2
E-Commerce Sales Analysis โ SQL Business Questions
Now that our database is ready, let's solve real-world business problems using SQL.
๐ Business Questions
1. Calculate Total Revenue
2. Count Total Orders
3. Count Total Customers
4. Count Total Products
5. Calculate Average Order Value (AOV)
6. Find Top 5 Selling Products
7. Find Revenue by Product Category
8. Find Top 5 Customers by Revenue
9. Calculate Monthly Revenue
10. Find Revenue by City
๐ฏ SQL Concepts Practiced:
Aggregate Functions, GROUP BY, ORDER BY, INNER JOIN, LIMIT, Date Functions, Business KPI Calculations
๐ก Double Tap โค๏ธ For More!
E-Commerce Sales Analysis โ SQL Business Questions
Now that our database is ready, let's solve real-world business problems using SQL.
๐ Business Questions
1. Calculate Total Revenue
SELECT SUM(quantity * unit_price) AS total_revenue
FROM order_items;
2. Count Total Orders
SELECT COUNT(*) AS total_orders
FROM orders;
3. Count Total Customers
SELECT COUNT(*) AS total_customers
FROM customers;
4. Count Total Products
SELECT COUNT(*) AS total_products
FROM products;
5. Calculate Average Order Value (AOV)
SELECT
ROUND(
SUM(quantity * unit_price) /
COUNT(DISTINCT order_id),
2
) AS average_order_value
FROM order_items;
6. Find Top 5 Selling Products
SELECT
p.product_name,
SUM(oi.quantity) AS total_quantity
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
GROUP BY p.product_name
ORDER BY total_quantity DESC
LIMIT 5;
7. Find Revenue by Product Category
SELECT
p.category,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
GROUP BY p.category
ORDER BY revenue DESC;
8. Find Top 5 Customers by Revenue
SELECT
c.customer_name,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_name
ORDER BY revenue DESC
LIMIT 5;
9. Calculate Monthly Revenue
SELECT
DATE_TRUNC('month', o.order_date) AS month,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY DATE_TRUNC('month', o.order_date)
ORDER BY month;
10. Find Revenue by City
SELECT
c.city,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.city
ORDER BY revenue DESC;
๐ฏ SQL Concepts Practiced:
Aggregate Functions, GROUP BY, ORDER BY, INNER JOIN, LIMIT, Date Functions, Business KPI Calculations
๐ก Double Tap โค๏ธ For More!
โค17
๐ ๐๐ฒ๐๐ ๐ฌ๐ผ๐๐ง๐๐ฏ๐ฒ ๐๐ต๐ฎ๐ป๐ป๐ฒ๐น๐ ๐๐ผ ๐๐ฒ๐ฎ๐ฟ๐ป ๐๐ฎ๐๐ฎ ๐๐ป๐ฎ๐น๐๐๐ถ๐ฐ๐ ๐
You donโt need expensive courses to learn SQL, Excel, Python, Power BI, Tableau, and real-world analytics projects.
The Best YouTube channels for Data Analytics can help you build job-ready skills for internships, placements, and full-time analyst roles โ all for FREE.
๐ ๐๐ป๐ฟ๐ผ๐น๐น ๐๐ผ๐ฟ ๐๐ฅ๐๐๐:
https://pdlink.in/3QO3MQB
๐Start with one channel, stay consistent, build projects, and your Data Analytics career can genuinely take off.
You donโt need expensive courses to learn SQL, Excel, Python, Power BI, Tableau, and real-world analytics projects.
The Best YouTube channels for Data Analytics can help you build job-ready skills for internships, placements, and full-time analyst roles โ all for FREE.
๐ ๐๐ป๐ฟ๐ผ๐น๐น ๐๐ผ๐ฟ ๐๐ฅ๐๐๐:
https://pdlink.in/3QO3MQB
๐Start with one channel, stay consistent, build projects, and your Data Analytics career can genuinely take off.
๐จ SQL Fact Most Beginners Learn Too Late!
Many people think
๐
- Works well when there are no
- Can return unexpected results if the subquery contains
๐
- Safely handles
- Often preferred for correlated subqueries.
- Commonly used in real-world SQL queries.
๐ฏ A favorite SQL interview concept for testing query logic!
โค๏ธ Drop a โค๏ธ if this helped, and follow for more SQL tips!
Many people think
NOT IN and NOT EXISTS always return the same result... but they don't. ๐
NOT IN
- Works well when there are no
NULL values.- Can return unexpected results if the subquery contains
NULL.๐
NOT EXISTS
- Safely handles
NULL values.- Often preferred for correlated subqueries.
- Commonly used in real-world SQL queries.
NULL values are involved, NOT EXISTS is usually the safer choice.๐ฏ A favorite SQL interview concept for testing query logic!
โค๏ธ Drop a โค๏ธ if this helped, and follow for more SQL tips!
โค5
๐ ๐๐ฅ๐๐ ๐ง๐๐ฆ ๐๐ฒ๐ฟ๐๐ถ๐ณ๐ถ๐ฐ๐ฎ๐๐ถ๐ผ๐ป | ๐๐ผ๐ผ๐๐ ๐ฌ๐ผ๐๐ฟ ๐๐ฎ๐ฟ๐ฒ๐ฒ๐ฟ๐
A FREE TCS certification can be a smart way to strengthen your profile, improve job readiness, and stand out in internships, placements, and fresher hiring.
โ Learn from one of Indiaโs top IT companies
โ Add a recognized certification to your resume + LinkedIn profile
โ Great for students, freshers, and placement preparation
โ Free certifications from trusted brands add real value to your profile
๐ ๐๐ป๐ฟ๐ผ๐น๐น ๐๐ผ๐ฟ ๐๐ฅ๐๐๐:
https://pdlink.in/4fjeMPe
๐Earn your free TCS certification. Make your resume stronger.
A FREE TCS certification can be a smart way to strengthen your profile, improve job readiness, and stand out in internships, placements, and fresher hiring.
โ Learn from one of Indiaโs top IT companies
โ Add a recognized certification to your resume + LinkedIn profile
โ Great for students, freshers, and placement preparation
โ Free certifications from trusted brands add real value to your profile
๐ ๐๐ป๐ฟ๐ผ๐น๐น ๐๐ผ๐ฟ ๐๐ฅ๐๐๐:
https://pdlink.in/4fjeMPe
๐Earn your free TCS certification. Make your resume stronger.
โค1
๐๐ฅ๐๐ ๐ฃ๐๐๐ต๐ผ๐ป ๐ฃ๐ฟ๐ผ๐ด๐ฟ๐ฎ๐บ๐บ๐ถ๐ป๐ด ๐๐ผ๐๐ฟ๐๐ฒ๐ | ๐ฐ ๐ ๐๐๐-๐ง๐ฎ๐ธ๐ฒ ๐๐ผ๐๐ฟ๐๐ฒ๐ ๐
โ Python is one of the most beginner-friendly and in-demand programming languages
๐Perfect For
๐จโ๐ Students
๐ผ Freshers
๐ซCoding Beginners
๐ Data / AI / Automation aspirants
๐ Anyone planning to start a tech career with Python
๐ ๐๐ป๐ฟ๐ผ๐น๐น ๐๐ผ๐ฟ ๐๐ฅ๐๐๐:
https://pdlink.in/4wjwEz2
๐ Build Python skills for free. Take your first step toward a stronger tech career.
โ Python is one of the most beginner-friendly and in-demand programming languages
๐Perfect For
๐จโ๐ Students
๐ผ Freshers
๐ซCoding Beginners
๐ Data / AI / Automation aspirants
๐ Anyone planning to start a tech career with Python
๐ ๐๐ป๐ฟ๐ผ๐น๐น ๐๐ผ๐ฟ ๐๐ฅ๐๐๐:
https://pdlink.in/4wjwEz2
๐ Build Python skills for free. Take your first step toward a stronger tech career.
โค1
SQL Project Series #3
E-Commerce Sales Analysis โ Intermediate SQL Business Questions
Let's solve more real-world business problems using SQL.
Business Questions
11. Find Repeat Customers
SELECT
customer_id,
COUNT(order_id) AS total_orders
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > 1;
12. Find Customers Who Never Placed an Order
SELECT
c.customer_id,
c.customer_name
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;
13. Find Inactive Customers (No Orders in the Last 90 Days)
SELECT
c.customer_id,
c.customer_name
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
HAVING MAX(o.order_date) < CURRENT_DATE - INTERVAL '90 days'
OR MAX(o.order_date) IS NULL;
14. Find the Best-Selling Product Category
SELECT
p.category,
SUM(oi.quantity) AS units_sold
FROM products p
JOIN order_items oi
ON p.product_id = oi.product_id
GROUP BY p.category
ORDER BY units_sold DESC
LIMIT 1;
15. Find the Highest Revenue Product
SELECT
p.product_name,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM products p
JOIN order_items oi
ON p.product_id = oi.product_id
GROUP BY p.product_name
ORDER BY revenue DESC
LIMIT 1;
16. Find the Lowest Revenue Product
SELECT
p.product_name,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM products p
JOIN order_items oi
ON p.product_id = oi.product_id
GROUP BY p.product_name
ORDER BY revenue
LIMIT 1;
17. Calculate Average Products per Order
SELECT
ROUND(AVG(product_count), 2) AS avg_products_per_order
FROM (
SELECT
order_id,
SUM(quantity) AS product_count
FROM order_items
GROUP BY order_id
) t;
18. Find Orders Worth More Than 10,000
SELECT
order_id,
SUM(quantity * unit_price) AS order_value
FROM order_items
GROUP BY order_id
HAVING SUM(quantity * unit_price) > 10000;
19. Find Customers with the Highest Average Order Value
SELECT
customer_id,
ROUND(AVG(order_value), 2) AS avg_order_value
FROM (
SELECT
o.customer_id,
o.order_id,
SUM(oi.quantity * oi.unit_price) AS order_value
FROM orders o
JOIN order_items oi
ON o.order_id = oi.order_id
GROUP BY o.customer_id, o.order_id
) t
GROUP BY customer_id
ORDER BY avg_order_value DESC;
20. Find the Top 3 Cities by Revenue
SELECT
c.city,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id
JOIN order_items oi
ON o.order_id = oi.order_id
GROUP BY c.city
ORDER BY revenue DESC
LIMIT 3;
SQL Concepts Practiced
โข LEFT JOIN
โข HAVING
โข Aggregate Functions
โข Nested Queries
โข GROUP BY
โข Business KPI Analysis
โข Customer Segmentation
โข Revenue Analysis
๐ก Double Tap โค๏ธ For More
E-Commerce Sales Analysis โ Intermediate SQL Business Questions
Let's solve more real-world business problems using SQL.
Business Questions
11. Find Repeat Customers
SELECT
customer_id,
COUNT(order_id) AS total_orders
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > 1;
12. Find Customers Who Never Placed an Order
SELECT
c.customer_id,
c.customer_name
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;
13. Find Inactive Customers (No Orders in the Last 90 Days)
SELECT
c.customer_id,
c.customer_name
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
HAVING MAX(o.order_date) < CURRENT_DATE - INTERVAL '90 days'
OR MAX(o.order_date) IS NULL;
14. Find the Best-Selling Product Category
SELECT
p.category,
SUM(oi.quantity) AS units_sold
FROM products p
JOIN order_items oi
ON p.product_id = oi.product_id
GROUP BY p.category
ORDER BY units_sold DESC
LIMIT 1;
15. Find the Highest Revenue Product
SELECT
p.product_name,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM products p
JOIN order_items oi
ON p.product_id = oi.product_id
GROUP BY p.product_name
ORDER BY revenue DESC
LIMIT 1;
16. Find the Lowest Revenue Product
SELECT
p.product_name,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM products p
JOIN order_items oi
ON p.product_id = oi.product_id
GROUP BY p.product_name
ORDER BY revenue
LIMIT 1;
17. Calculate Average Products per Order
SELECT
ROUND(AVG(product_count), 2) AS avg_products_per_order
FROM (
SELECT
order_id,
SUM(quantity) AS product_count
FROM order_items
GROUP BY order_id
) t;
18. Find Orders Worth More Than 10,000
SELECT
order_id,
SUM(quantity * unit_price) AS order_value
FROM order_items
GROUP BY order_id
HAVING SUM(quantity * unit_price) > 10000;
19. Find Customers with the Highest Average Order Value
SELECT
customer_id,
ROUND(AVG(order_value), 2) AS avg_order_value
FROM (
SELECT
o.customer_id,
o.order_id,
SUM(oi.quantity * oi.unit_price) AS order_value
FROM orders o
JOIN order_items oi
ON o.order_id = oi.order_id
GROUP BY o.customer_id, o.order_id
) t
GROUP BY customer_id
ORDER BY avg_order_value DESC;
20. Find the Top 3 Cities by Revenue
SELECT
c.city,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id
JOIN order_items oi
ON o.order_id = oi.order_id
GROUP BY c.city
ORDER BY revenue DESC
LIMIT 3;
SQL Concepts Practiced
โข LEFT JOIN
โข HAVING
โข Aggregate Functions
โข Nested Queries
โข GROUP BY
โข Business KPI Analysis
โข Customer Segmentation
โข Revenue Analysis
๐ก Double Tap โค๏ธ For More
โค8
๐๐ถ๐ฐ๐ธ๐๐๐ฎ๐ฟ๐ ๐ฌ๐ผ๐๐ฟ ๐๐ ๐๐ผ๐๐ฟ๐ป๐ฒ๐ | ๐ฑ ๐ ๐๐๐-๐ช๐ฎ๐๐ฐ๐ต ๐๐ฅ๐๐ ๐ฉ๐ถ๐ฑ๐ฒ๐ผ๐ ๐
The good news is โ you donโt need expensive courses to understand the basics of AI, Machine Learning, Neural Networks, Prompting, and real-world AI tools.
This guide features 5 must-watch FREE AI videos that can help you build a strong foundation in AI concepts
๐ ๐๐ป๐ฟ๐ผ๐น๐น ๐๐ผ๐ฟ ๐๐ฅ๐๐๐:
https://pdlink.in/4gn4LS5
๐ Start watching today. Learn AI step by step. Build future-ready skills for free.
The good news is โ you donโt need expensive courses to understand the basics of AI, Machine Learning, Neural Networks, Prompting, and real-world AI tools.
This guide features 5 must-watch FREE AI videos that can help you build a strong foundation in AI concepts
๐ ๐๐ป๐ฟ๐ผ๐น๐น ๐๐ผ๐ฟ ๐๐ฅ๐๐๐:
https://pdlink.in/4gn4LS5
๐ Start watching today. Learn AI step by step. Build future-ready skills for free.
โค1
SQL Project Series #4
E-Commerce Sales Analysis โ Advanced SQL with Window Functions ๐
Window functions are widely used by Data Analysts to calculate rankings, running totals, moving averages, and customer insights without losing row-level details.
Business Questions
21. Rank Customers by Total Revenue
WITH customer_revenue AS (
SELECT
o.customer_id,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.customer_id
)
SELECT
customer_id,
revenue,
DENSE_RANK() OVER (ORDER BY revenue DESC) AS revenue_rank
FROM customer_revenue;
22. Find the Top Selling Product in Each Category
WITH product_sales AS (
SELECT
p.category,
p.product_name,
SUM(oi.quantity) AS total_sold
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.category, p.product_name
)
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY category ORDER BY total_sold DESC
) AS rn
FROM product_sales
) t
WHERE rn = 1;
23. Calculate Running Revenue by Order Date
WITH daily_sales AS (
SELECT
o.order_date,
SUM(oi.quantity * oi.unit_price) AS daily_revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_date
)
SELECT
order_date,
daily_revenue,
SUM(daily_revenue) OVER (ORDER BY order_date) AS running_revenue
FROM daily_sales;
24. Find the Previous Order Date for Each Customer
SELECT
customer_id,
order_id,
order_date,
LAG(order_date) OVER (
PARTITION BY customer_id ORDER BY order_date
) AS previous_order_date
FROM orders;
25. Find the Next Order Date for Each Customer
SELECT
customer_id,
order_id,
order_date,
LEAD(order_date) OVER (
PARTITION BY customer_id ORDER BY order_date
) AS next_order_date
FROM orders;
26. Calculate Days Between Consecutive Orders
SELECT
customer_id,
order_date,
order_date - LAG(order_date) OVER (
PARTITION BY customer_id ORDER BY order_date
) AS days_between_orders
FROM orders;
Note: For Postgres use order_date - LAG(order_date) OVER(...). For MySQL use DATEDIFF(order_date, LAG(order_date) OVER(...))
27. Find the Top 3 Customers by Revenue
WITH customer_revenue AS (
SELECT
o.customer_id,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.customer_id
)
SELECT *
FROM (
SELECT *,
DENSE_RANK() OVER (ORDER BY revenue DESC) AS rnk
FROM customer_revenue
) t
WHERE rnk <= 3;
28. Find Each Product's Contribution to Total Revenue
WITH product_revenue AS (
SELECT
p.product_name,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_name
)
SELECT
product_name,
revenue,
ROUND(100.0 * revenue / SUM(revenue) OVER (), 2) AS revenue_percentage
FROM product_revenue
ORDER BY revenue DESC;
E-Commerce Sales Analysis โ Advanced SQL with Window Functions ๐
Window functions are widely used by Data Analysts to calculate rankings, running totals, moving averages, and customer insights without losing row-level details.
Business Questions
21. Rank Customers by Total Revenue
WITH customer_revenue AS (
SELECT
o.customer_id,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.customer_id
)
SELECT
customer_id,
revenue,
DENSE_RANK() OVER (ORDER BY revenue DESC) AS revenue_rank
FROM customer_revenue;
22. Find the Top Selling Product in Each Category
WITH product_sales AS (
SELECT
p.category,
p.product_name,
SUM(oi.quantity) AS total_sold
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.category, p.product_name
)
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY category ORDER BY total_sold DESC
) AS rn
FROM product_sales
) t
WHERE rn = 1;
23. Calculate Running Revenue by Order Date
WITH daily_sales AS (
SELECT
o.order_date,
SUM(oi.quantity * oi.unit_price) AS daily_revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_date
)
SELECT
order_date,
daily_revenue,
SUM(daily_revenue) OVER (ORDER BY order_date) AS running_revenue
FROM daily_sales;
24. Find the Previous Order Date for Each Customer
SELECT
customer_id,
order_id,
order_date,
LAG(order_date) OVER (
PARTITION BY customer_id ORDER BY order_date
) AS previous_order_date
FROM orders;
25. Find the Next Order Date for Each Customer
SELECT
customer_id,
order_id,
order_date,
LEAD(order_date) OVER (
PARTITION BY customer_id ORDER BY order_date
) AS next_order_date
FROM orders;
26. Calculate Days Between Consecutive Orders
SELECT
customer_id,
order_date,
order_date - LAG(order_date) OVER (
PARTITION BY customer_id ORDER BY order_date
) AS days_between_orders
FROM orders;
Note: For Postgres use order_date - LAG(order_date) OVER(...). For MySQL use DATEDIFF(order_date, LAG(order_date) OVER(...))
27. Find the Top 3 Customers by Revenue
WITH customer_revenue AS (
SELECT
o.customer_id,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.customer_id
)
SELECT *
FROM (
SELECT *,
DENSE_RANK() OVER (ORDER BY revenue DESC) AS rnk
FROM customer_revenue
) t
WHERE rnk <= 3;
28. Find Each Product's Contribution to Total Revenue
WITH product_revenue AS (
SELECT
p.product_name,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_name
)
SELECT
product_name,
revenue,
ROUND(100.0 * revenue / SUM(revenue) OVER (), 2) AS revenue_percentage
FROM product_revenue
ORDER BY revenue DESC;
โค3
29. Find Monthly Revenue Growth
WITH monthly_sales AS (
SELECT
DATE_TRUNC('month', o.order_date) AS month,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY DATE_TRUNC('month', o.order_date)
)
SELECT
month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS previous_month_revenue,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month), 2) AS growth_percentage
FROM monthly_sales;
30. Find the Highest Value Order for Each Customer
WITH order_values AS (
SELECT
o.customer_id,
o.order_id,
SUM(oi.quantity * oi.unit_price) AS order_value
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.customer_id, o.order_id
)
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY customer_id ORDER BY order_value DESC
) AS rn
FROM order_values
) t
WHERE rn = 1;
Window Functions Covered
โข Ranking: ROW_NUMBER(), RANK(), DENSE_RANK()
โข Navigation: LAG(), LEAD()
โข Aggregates: SUM() OVER()
โข Analytics: Running Totals, Revenue Contribution, Month-over-Month Growth
๐ก Double Tap โค๏ธ For More
WITH monthly_sales AS (
SELECT
DATE_TRUNC('month', o.order_date) AS month,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY DATE_TRUNC('month', o.order_date)
)
SELECT
month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS previous_month_revenue,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month), 2) AS growth_percentage
FROM monthly_sales;
30. Find the Highest Value Order for Each Customer
WITH order_values AS (
SELECT
o.customer_id,
o.order_id,
SUM(oi.quantity * oi.unit_price) AS order_value
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.customer_id, o.order_id
)
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY customer_id ORDER BY order_value DESC
) AS rn
FROM order_values
) t
WHERE rn = 1;
Window Functions Covered
โข Ranking: ROW_NUMBER(), RANK(), DENSE_RANK()
โข Navigation: LAG(), LEAD()
โข Aggregates: SUM() OVER()
โข Analytics: Running Totals, Revenue Contribution, Month-over-Month Growth
๐ก Double Tap โค๏ธ For More
โค4
๐ SQL Project Series #5
E-Commerce Sales Analysis โ Advanced Business Analytics
In this part, we'll solve real-world business problems that Data Analysts encounter while working with customer, sales, and product data.
31. Calculate Customer Lifetime Value (CLV)
SELECT
o.customer_id,
SUM(oi.quantity * oi.unit_price) AS customer_lifetime_value
FROM orders o
JOIN order_items oi
ON o.order_id = oi.order_id
GROUP BY o.customer_id
ORDER BY customer_lifetime_value DESC;
32. Calculate Repeat Purchase Rate
WITH customer_orders AS (
SELECT
customer_id,
COUNT(*) AS total_orders
FROM orders
GROUP BY customer_id
)
SELECT
ROUND(
100.0 *
COUNT(CASE WHEN total_orders > 1 THEN 1 END) /
COUNT(*),
2
) AS repeat_purchase_rate
FROM customer_orders;
33. Find New vs Returning Customers
WITH first_order AS (
SELECT
customer_id,
MIN(order_date) AS first_order_date
FROM orders
GROUP BY customer_id
)
SELECT
CASE
WHEN o.order_date = f.first_order_date
THEN 'New Customer'
ELSE 'Returning Customer'
END AS customer_type,
COUNT(*) AS total_orders
FROM orders o
JOIN first_order f
ON o.customer_id = f.customer_id
GROUP BY customer_type;
34. Find Customer Retention by Month
WITH monthly_orders AS (
SELECT DISTINCT
customer_id,
DATE_TRUNC('month', order_date) AS order_month
FROM orders
)
SELECT
order_month,
COUNT(DISTINCT customer_id) AS active_customers
FROM monthly_orders
GROUP BY order_month
ORDER BY order_month;
35. Find Customers Who Purchased from Multiple Categories
SELECT
o.customer_id,
COUNT(DISTINCT p.category) AS categories_purchased
FROM orders o
JOIN order_items oi
ON o.order_id = oi.order_id
JOIN products p
ON oi.product_id = p.product_id
GROUP BY o.customer_id
HAVING COUNT(DISTINCT p.category) > 1;
36. Find the Most Frequently Purchased Product Pair
SELECT
oi1.product_id AS productโ,
oi2.product_id AS productโ,
COUNT(*) AS purchase_count
FROM order_items oi1
JOIN order_items oi2
ON oi1.order_id = oi2.order_id
AND oi1.product_id < oi2.product_id
GROUP BY oi1.product_id, oi2.product_id
ORDER BY purchase_count DESC
LIMIT 10;
37. Calculate Average Days Between Orders
WITH customer_orders AS (
SELECT
customer_id,
order_date,
LAG(order_date) OVER (
PARTITION BY customer_id
ORDER BY order_date
) AS previous_order
FROM orders
)
SELECT
customer_id,
ROUND(
AVG(order_date - previous_order),
2
) AS avg_days_between_orders
FROM customer_orders
WHERE previous_order IS NOT NULL
GROUP BY customer_id;
38. Find the Fastest Growing Product Category
WITH monthly_category_sales AS (
SELECT
DATE_TRUNC('month', o.order_date) AS month,
p.category,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi
ON o.order_id = oi.order_id
JOIN products p
ON oi.product_id = p.product_id
GROUP BY month, p.category
)
SELECT
month,
category,
revenue,
revenue -
LAG(revenue) OVER (
PARTITION BY category
ORDER BY month
) AS revenue_growth
FROM monthly_category_sales;
39. Identify Customers at Risk of Churn
SELECT
customer_id,
MAX(order_date) AS last_order_date
FROM orders
GROUP BY customer_id
HAVING MAX(order_date) <
CURRENT_DATE - INTERVAL '90 days';
40. Perform RFM Analysis
SELECT
customer_id,
CURRENT_DATE - MAX(order_date) AS recency,
COUNT(order_id) AS frequency,
SUM(oi.quantity * oi.unit_price) AS monetary
FROM orders o
JOIN order_items oi
ON o.order_id = oi.order_id
GROUP BY customer_id
ORDER BY monetary DESC;
๐ก Double Tap โค๏ธ For More
E-Commerce Sales Analysis โ Advanced Business Analytics
In this part, we'll solve real-world business problems that Data Analysts encounter while working with customer, sales, and product data.
31. Calculate Customer Lifetime Value (CLV)
SELECT
o.customer_id,
SUM(oi.quantity * oi.unit_price) AS customer_lifetime_value
FROM orders o
JOIN order_items oi
ON o.order_id = oi.order_id
GROUP BY o.customer_id
ORDER BY customer_lifetime_value DESC;
32. Calculate Repeat Purchase Rate
WITH customer_orders AS (
SELECT
customer_id,
COUNT(*) AS total_orders
FROM orders
GROUP BY customer_id
)
SELECT
ROUND(
100.0 *
COUNT(CASE WHEN total_orders > 1 THEN 1 END) /
COUNT(*),
2
) AS repeat_purchase_rate
FROM customer_orders;
33. Find New vs Returning Customers
WITH first_order AS (
SELECT
customer_id,
MIN(order_date) AS first_order_date
FROM orders
GROUP BY customer_id
)
SELECT
CASE
WHEN o.order_date = f.first_order_date
THEN 'New Customer'
ELSE 'Returning Customer'
END AS customer_type,
COUNT(*) AS total_orders
FROM orders o
JOIN first_order f
ON o.customer_id = f.customer_id
GROUP BY customer_type;
34. Find Customer Retention by Month
WITH monthly_orders AS (
SELECT DISTINCT
customer_id,
DATE_TRUNC('month', order_date) AS order_month
FROM orders
)
SELECT
order_month,
COUNT(DISTINCT customer_id) AS active_customers
FROM monthly_orders
GROUP BY order_month
ORDER BY order_month;
35. Find Customers Who Purchased from Multiple Categories
SELECT
o.customer_id,
COUNT(DISTINCT p.category) AS categories_purchased
FROM orders o
JOIN order_items oi
ON o.order_id = oi.order_id
JOIN products p
ON oi.product_id = p.product_id
GROUP BY o.customer_id
HAVING COUNT(DISTINCT p.category) > 1;
36. Find the Most Frequently Purchased Product Pair
SELECT
oi1.product_id AS productโ,
oi2.product_id AS productโ,
COUNT(*) AS purchase_count
FROM order_items oi1
JOIN order_items oi2
ON oi1.order_id = oi2.order_id
AND oi1.product_id < oi2.product_id
GROUP BY oi1.product_id, oi2.product_id
ORDER BY purchase_count DESC
LIMIT 10;
37. Calculate Average Days Between Orders
WITH customer_orders AS (
SELECT
customer_id,
order_date,
LAG(order_date) OVER (
PARTITION BY customer_id
ORDER BY order_date
) AS previous_order
FROM orders
)
SELECT
customer_id,
ROUND(
AVG(order_date - previous_order),
2
) AS avg_days_between_orders
FROM customer_orders
WHERE previous_order IS NOT NULL
GROUP BY customer_id;
38. Find the Fastest Growing Product Category
WITH monthly_category_sales AS (
SELECT
DATE_TRUNC('month', o.order_date) AS month,
p.category,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi
ON o.order_id = oi.order_id
JOIN products p
ON oi.product_id = p.product_id
GROUP BY month, p.category
)
SELECT
month,
category,
revenue,
revenue -
LAG(revenue) OVER (
PARTITION BY category
ORDER BY month
) AS revenue_growth
FROM monthly_category_sales;
39. Identify Customers at Risk of Churn
SELECT
customer_id,
MAX(order_date) AS last_order_date
FROM orders
GROUP BY customer_id
HAVING MAX(order_date) <
CURRENT_DATE - INTERVAL '90 days';
40. Perform RFM Analysis
SELECT
customer_id,
CURRENT_DATE - MAX(order_date) AS recency,
COUNT(order_id) AS frequency,
SUM(oi.quantity * oi.unit_price) AS monetary
FROM orders o
JOIN order_items oi
ON o.order_id = oi.order_id
GROUP BY customer_id
ORDER BY monetary DESC;
๐ก Double Tap โค๏ธ For More
โค2
๐ Essential SQL Concepts Every Data Analyst Must Know
๐ SQL is the most important skill for Data Analysts. Almost every analytics job requires working with databases to extract, filter, analyze, and summarize data.
Understanding the following SQL concepts will help you write efficient queries and solve real business problems with data.
1๏ธโฃ SELECT Statement (Data Retrieval)
What it is: Retrieves data from a table.
Use cases: Retrieving specific columns, viewing datasets, extracting required information.
2๏ธโฃ WHERE Clause (Filtering Data)
What it is: Filters rows based on specific conditions.
Common conditions: =, >, <, >=, <=, BETWEEN, IN, LIKE
3๏ธโฃ ORDER BY (Sorting Data)
What it is: Sorts query results in ascending or descending order.
Sorting options: ASC (default), DESC
4๏ธโฃ GROUP BY (Aggregation)
What it is: Groups rows with same values into summary rows.
Use cases: Sales per region, customers per country, orders per product category.
5๏ธโฃ Aggregate Functions
What they do: Perform calculations on multiple rows.
Common functions: COUNT(), SUM(), AVG(), MIN(), MAX()
6๏ธโฃ HAVING Clause
What it is: Filters grouped data after aggregation.
Key difference: WHERE filters rows before grouping, HAVING filters groups after aggregation.
7๏ธโฃ SQL JOINS (Combining Tables)
What they do: Combine tables.
-- INNER JOIN
-- LEFT JOIN
Common types: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN
8๏ธโฃ Subqueries
What it is: Query inside another query.
Use cases: Comparing values, filtering based on aggregated results.
9๏ธโฃ Common Table Expressions (CTE)
What it is: Temporary result set used inside a query.
Benefits: Cleaner queries, easier debugging, better readability.
๐ Window Functions
What they do: Perform calculations across rows related to current row.
Common functions: ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD()
Why SQL is Critical for Data Analysts
โข Extract data from databases
โข Analyze large datasets efficiently
โข Generate reports and dashboards
โข Support business decision-making
SQL Resources: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v
Double Tap โฅ๏ธ For More
๐ SQL is the most important skill for Data Analysts. Almost every analytics job requires working with databases to extract, filter, analyze, and summarize data.
Understanding the following SQL concepts will help you write efficient queries and solve real business problems with data.
1๏ธโฃ SELECT Statement (Data Retrieval)
What it is: Retrieves data from a table.
SELECT name, salary
FROM employees;
Use cases: Retrieving specific columns, viewing datasets, extracting required information.
2๏ธโฃ WHERE Clause (Filtering Data)
What it is: Filters rows based on specific conditions.
SELECT *
FROM orders
WHERE order_amount > 500;
Common conditions: =, >, <, >=, <=, BETWEEN, IN, LIKE
3๏ธโฃ ORDER BY (Sorting Data)
What it is: Sorts query results in ascending or descending order.
SELECT name, salary
FROM employees
ORDER BY salary DESC;
Sorting options: ASC (default), DESC
4๏ธโฃ GROUP BY (Aggregation)
What it is: Groups rows with same values into summary rows.
SELECT department, COUNT(*)
FROM employees
GROUP BY department;
Use cases: Sales per region, customers per country, orders per product category.
5๏ธโฃ Aggregate Functions
What they do: Perform calculations on multiple rows.
SELECT AVG(salary)
FROM employees;
Common functions: COUNT(), SUM(), AVG(), MIN(), MAX()
6๏ธโฃ HAVING Clause
What it is: Filters grouped data after aggregation.
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;
Key difference: WHERE filters rows before grouping, HAVING filters groups after aggregation.
7๏ธโฃ SQL JOINS (Combining Tables)
What they do: Combine tables.
-- INNER JOIN
SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.customer_id;
-- LEFT JOIN
SELECT customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;
Common types: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN
8๏ธโฃ Subqueries
What it is: Query inside another query.
SELECT name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
Use cases: Comparing values, filtering based on aggregated results.
9๏ธโฃ Common Table Expressions (CTE)
What it is: Temporary result set used inside a query.
WITH high_salary AS (
SELECT name, salary
FROM employees
WHERE salary > 70000
)
SELECT *
FROM high_salary;
Benefits: Cleaner queries, easier debugging, better readability.
๐ Window Functions
What they do: Perform calculations across rows related to current row.
SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;
Common functions: ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD()
Why SQL is Critical for Data Analysts
โข Extract data from databases
โข Analyze large datasets efficiently
โข Generate reports and dashboards
โข Support business decision-making
SQL Resources: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v
Double Tap โฅ๏ธ For More
โค5