Endi mavjud! Telegram Tadqiqoti 2025 โ yilning asosiy insaytlari 
SQL Programming Resources
Kanalga Telegramโda oโtish
Find top SQL resources from global universities, cool projects, and learning materials for data analytics. Admin: @coderfun Useful links: heylink.me/DataAnalytics Promotions: @love_data
Ko'proq ko'rsatish๐ Telegram kanali SQL Programming Resources analitikasi
SQL Programming Resources (@sqlanalyst) Ingliz til segmentidagi kanali faol ishtirokchi. Hozirda hamjamiyat 76 022 obunachidan iborat bo'lib, Texnologiyalar & Aralashmalar toifasida 1 692-o'rinni va Hindiston mintaqasida 4 139-o'rinni egallagan.
๐ Auditoriya koโrsatkichlari va dinamika
ะฝะตะฒัะดะพะผะพ sanasidan buyon loyiha tez oโsib, 76 022 obunachiga ega boโldi.
30 Iyun, 2026 dagi oxirgi maโlumotlarga koโra kanal barqaror faollikka ega. Oxirgi 30 kunda obunachilar soni 387 ga, soโnggi 24 soatda esa 39 ga oโzgardi va umumiy qamrov yuqori darajada qolmoqda.
- Tasdiqlash holati: Tasdiqlanmagan
- Jalb etish (ER): Auditoriya oโrtacha 2.34% darajada jalb etiladi. Nashrdan keyingi dastlabki 24 soatda kontent odatda umumiy obunachilar sonining 1.23% ini tashkil etuvchi reaksiyalarni toโplaydi.
- Post qamrovi: Har bir post oโrtacha 1 779 marta koโriladi; birinchi sutkada odatda 934 ta koโrish yigโiladi.
- Reaksiyalar va oโzaro taโsir: Auditoriya faol: har bir postga oโrtacha 3 ta reaksiya keladi.
- Tematik yoโnalishlar: Kontent row, sql, customer_id, logic, desc kabi asosiy mavzularga jamlangan.
๐ Tavsif va kontent siyosati
Muallif resursni shaxsiy fikrni ifoda etish maydoni sifatida taโriflaydi:
โFind top SQL resources from global universities, cool projects, and learning materials for data analytics.
Admin: @coderfun
Useful links: heylink.me/DataAnalytics
Promotions: @love_dataโ
Yuqori yangilanish chastotasi (oxirgi maโlumot 01 Iyul, 2026 da olingan) sababli kanal doimo dolzarb va katta qamrovli boโlib qoladi. Analitika auditoriya kontent bilan faol hamkorlik qilishini, uni Texnologiyalar & Aralashmalar toifasidagi muhim taโsir nuqtasiga aylantirishini koโrsatadi.
76 022
Obunachilar
+3924 soatlar
+927 kunlar
+38730 kunlar
Ma'lumot yuklanmoqda...
O'xshash kanallar
Taglar buluti
Kirish va chiqish esdaliklari
---
---
---
---
---
---
Obunachilarni jalb qilish
Iyul '26
Iyul '26
+9
0 kanalda
Iyun '26
+550
8 kanalda
Get PRO
May '26
+597
9 kanalda
Get PRO
Aprel '26
+494
4 kanalda
Get PRO
Mart '26
+166
6 kanalda
Get PRO
Fevral '26
+741
13 kanalda
Get PRO
Yanvar '26
+716
5 kanalda
Get PRO
Dekabr '25
+863
4 kanalda
Get PRO
Noyabr '25
+771
6 kanalda
Get PRO
Oktabr '25
+676
4 kanalda
Get PRO
Sentabr '25
+468
12 kanalda
Get PRO
Avgust '25
+502
25 kanalda
Get PRO
Iyul '25
+625
21 kanalda
Get PRO
Iyun '25
+1 033
37 kanalda
Get PRO
May '25
+2 110
26 kanalda
Get PRO
Aprel '25
+3 784
40 kanalda
Get PRO
Mart '25
+1 190
27 kanalda
Get PRO
Fevral '25
+881
30 kanalda
Get PRO
Yanvar '25
+1 239
36 kanalda
Get PRO
Dekabr '24
+1 240
22 kanalda
Get PRO
Noyabr '24
+3 503
23 kanalda
Get PRO
Oktabr '24
+3 975
29 kanalda
Get PRO
Sentabr '24
+4 469
26 kanalda
Get PRO
Avgust '24
+5 990
23 kanalda
Get PRO
Iyul '24
+8 180
25 kanalda
Get PRO
Iyun '24
+7 119
22 kanalda
Get PRO
May '24
+5 879
12 kanalda
Get PRO
Aprel '24
+5 286
13 kanalda
Get PRO
Mart '24
+6 156
17 kanalda
Get PRO
Fevral '24
+4 581
8 kanalda
Get PRO
Yanvar '24
+5 285
4 kanalda
Get PRO
Dekabr '23
+7 504
8 kanalda
| Sana | Obunachilarni jalb qilish | Esdaliklar | Kanallar | |
| 01 Iyul | +9 |
Kanal postlari
๐ ๐๐ฟ๐ฒ๐ฒ ๐ฆ๐ค๐ ๐๐ฒ๐ฟ๐๐ถ๐ณ๐ถ๐ฐ๐ฎ๐๐ถ๐ผ๐ป ๐ณ๐ผ๐ฟ ๐๐ฎ๐๐ฎ ๐ฆ๐ฐ๐ถ๐ฒ๐ป๐ฐ๐ฒ ๐๐ป
This FREE SQL certification program is perfect for students, freshers, and aspiring data professionals ๐ฅ
๐ก Why Learn SQL?
โจ One of the Most In-Demand Tech Skills
โจ Essential for Data Analytics & Data Science
โจ Used by Top IT & Tech Companies
โจ Boosts Career Opportunities in 2026
๐ ๐๐ป๐ฟ๐ผ๐น๐น ๐๐ผ๐ฟ ๐๐ฅ๐๐๐:
https://pdlink.in/4vspUif
๐ฅ Start learning SQL today and prepare for high-paying careers in Data Analytics & Data Science.
| 2 | ๐ 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 | 810 |
| 3 | ๐ 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; | 619 |
| 4 |  ๐ช๐ฎ๐น๐บ๐ฎ๐ฟ๐ ๐๐ฅ๐๐ ๐๐ป๐๐ฒ๐ฟ๐ป๐๐ต๐ถ๐ฝ ๐๐ฒ๐ฟ๐๐ถ๐ณ๐ถ๐ฐ๐ฎ๐๐ถ๐ผ๐ป ๐ฃ๐ฟ๐ผ๐ด๐ฟ๐ฎ๐บ | ๐๐ฝ๐ฝ๐น๐ ๐ก๐ผ๐!๐
Offering a FREE Advanced Software Engineering Job Simulation where you can work on practical tasks, enhance your coding skills, and earn a certificate to strengthen your resume.
๐ฏ Benefits:
โ
Free Certificate
โ
Real-World Software Engineering Tasks
โ
Self-Paced Learning
Don't miss this opportunity to boost your profile and get job-ready for top tech companies! ๐ฅ
๐๐ป๐ฟ๐ผ๐น๐น ๐๐ผ๐ฟ ๐๐ฅ๐๐๐:
https://pdlink.in/4vDJN5W
๐ข Share with your friends and classmates. | 780 |
| 5 |  ๐๐ฅ๐๐ ๐๐ & ๐ ๐ฎ๐ฐ๐ต๐ถ๐ป๐ฒ ๐๐ฒ๐ฎ๐ฟ๐ป๐ถ๐ป๐ด ๐ฅ๐ฒ๐๐ผ๐๐ฟ๐ฐ๐ฒ๐ | ๐ฐ ๐๐ฒ๐๐ ๐ฌ๐ผ๐๐ง๐๐ฏ๐ฒ ๐๐ต๐ฎ๐ป๐ป๐ฒ๐น๐ ๐
Learn Artificial Intelligence and Machine Learning for FREE from world-class creators
โ๏ธ 100% Free Learning
โ๏ธ Beginner to Advanced Content
โ๏ธ Real-World Coding Projects
โ๏ธ Learn from AI Experts
โ๏ธ Build a Strong Portfolio
โ๏ธ Stay Updated with the Latest AI Trends
๐ ๐๐ป๐ฟ๐ผ๐น๐น ๐๐ผ๐ฟ ๐๐ฅ๐๐๐:
https://pdlinks.in/aiml
๐Start Learning Today. Build AI Skills. Get Career Ready! | 1 023 |
| 6 | 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 120 |
| 7 |  ๐ป ๐ ๐ฎ๐๐๐ฒ๐ฟ ๐ฆ๐ค๐ ๐๐ข๐ฅ ๐๐ฅ๐๐ | ๐ฑ ๐๐บ๐ฎ๐๐ถ๐ป๐ด ๐ช๐ฒ๐ฏ๐๐ถ๐๐ฒ๐ ๐ง๐ผ ๐๐ฒ๐ฎ๐ฟ๐ป ๐ฆ๐ค๐ ๐
Want to become a Data Analyst, Data Scientist, or Software Engineer? Start by mastering SQLโone of the most in-demand skills in the tech industry!
These 5 FREE websites will help you learn SQL from scratch through interactive lessons, quizzes, and hands-on practice.
๐๐ข๐ง๐ค๐:-
https://pdlinks.in/qje
๐ Start Learning SQL Today and Build a Strong Foundation for Your Tech Career! | 1 108 |
| 8 |  ๐ ๐ก๐ฉ๐๐๐๐ ๐๐ฅ๐๐ ๐๐ ๐๐ฒ๐ฟ๐๐ถ๐ณ๐ถ๐ฐ๐ฎ๐๐ถ๐ผ๐ป ๐๐ผ๐๐ฟ๐๐ฒ๐ | ๐๐ฒ๐ฎ๐ฟ๐ป ๐๐ฟ๐ผ๐บ ๐๐ ๐๐ป๐ฑ๐๐๐๐ฟ๐ ๐๐ฒ๐ฎ๐ฑ๐ฒ๐ฟ๐
Want to build cutting-edge *AI skills* from one of the world's leading AI and GPU companies?
*NVIDIA* offers *FREE AI Certification Courses* to help students, freshers, developers, and professionals
๐ ๐๐ป๐ฟ๐ผ๐น๐น ๐๐ผ๐ฟ ๐๐ฅ๐๐๐:
https://pdlinks.in/nvdia
๐ Start Learning Today. Earn Your Certificate. Build Your Future in AI! | 1 355 |
| 9 | ๐ 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 | 1 107 |
| 10 | ๐ SQL Scenario-Based Interview Questions with Answers: Part-7
๐ Question 61: Find the Longest Purchase Streak
Table: orders (customer_id, order_date)
Requirement: Find the longest consecutive daily purchase streak for each customer.
WITH purchase_days AS (
SELECT DISTINCT
customer_id,
order_date
FROM orders
),
streaks AS (
SELECT
customer_id,
order_date,
order_date -
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date
) * INTERVAL '1 day' AS grp
FROM purchase_days
)
SELECT
customer_id,
COUNT(*) AS longest_streak
FROM streaks
GROUP BY customer_id, grp
ORDER BY longest_streak DESC;
๐ Question 62: Find Products Purchased by More Than 80% of Customers
Tables:
products (product_id)
orders (customer_id, product_id)
SELECT
product_id
FROM orders
GROUP BY product_id
HAVING COUNT(DISTINCT customer_id) >=
(
SELECT COUNT(DISTINCT customer_id) * 0.80
FROM orders
);
๐ Question 63: Find the Highest Revenue Day for Each Month
Table: sales (sale_date, amount)
WITH daily_sales AS (
SELECT
DATE(sale_date) AS sale_day,
SUM(amount) AS revenue
FROM sales
GROUP BY DATE(sale_date)
)
SELECT
month,
sale_day,
revenue
FROM (
SELECT
DATE_TRUNC('month', sale_day) AS month,
sale_day,
revenue,
DENSE_RANK() OVER (
PARTITION BY DATE_TRUNC('month', sale_day)
ORDER BY revenue DESC
) AS rnk
FROM daily_sales
) t
WHERE rnk = 1;
๐ Question 64: Calculate Customer Purchase Frequency
Table: orders (customer_id, order_date)
Requirement: Average number of days between consecutive orders.
WITH purchase_gap 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 purchase_gap
WHERE previous_order IS NOT NULL
GROUP BY customer_id;
๐ Question 65: Find Customers Who Bought Only One Product Category
Tables:
orders (customer_id, product_id)
products (product_id, category)
SELECT
customer_id
FROM orders o
JOIN products p
ON o.product_id = p.product_id
GROUP BY customer_id
HAVING COUNT(DISTINCT category) = 1;
๐ Question 66: Calculate Revenue by Week
Table: sales (sale_date, amount)
SELECT
DATE_TRUNC('week', sale_date) AS week,
SUM(amount) AS revenue
FROM sales
GROUP BY DATE_TRUNC('week', sale_date)
ORDER BY week;
๐ Question 67: Find Customers with the Highest Average Order Value
Table: orders (customer_id, amount)
SELECT
customer_id,
ROUND(AVG(amount), 2) AS avg_order_value
FROM orders
GROUP BY customer_id
ORDER BY avg_order_value DESC
LIMIT 10;
๐ Question 68: Find the Month with the Highest Number of New Customers
Table: users (user_id, signup_date)
SELECT
DATE_TRUNC('month', signup_date) AS month,
COUNT(*) AS new_customers
FROM users
GROUP BY DATE_TRUNC('month', signup_date)
ORDER BY new_customers DESC
LIMIT 1; | 884 |
| 11 |  ๐ง๐๐ฆ ๐๐ฅ๐๐ ๐๐ฒ๐ฟ๐๐ถ๐ณ๐ถ๐ฐ๐ฎ๐๐ถ๐ผ๐ป ๐ข๐ป ๐๐ฎ๐๐ฎ ๐ ๐ฎ๐ป๐ฎ๐ด๐ฒ๐บ๐ฒ๐ป๐ - ๐๐ป๐ฟ๐ผ๐น๐น ๐๐ผ๐ฟ ๐๐ฅ๐๐๐
TCS iON is offering a FREE Master Data Management Course with a Certificate,
โ
100% FREE Learning
โ
Certificate on Completion
โ
Self-Paced Online Course
โ
Beginner-Friendly Content
โ
Industry-Relevant Skills
โ
Resume & LinkedIn Profile Boost
๐ ๐๐ป๐ฟ๐ผ๐น๐น ๐๐ผ๐ฟ ๐๐ฅ๐๐๐:
https://pdlink.in/4jGFBw0
๐ Start Learning Today. Upskill for Free. Get Career Ready! | 1 125 |
| 12 | ๐ SQL Business Scenario Interview Questions with Answers Part 6
๐ Question 51: Find Customers Who Haven't Ordered in the Last 90 Days
Tables: customers (customer_id), orders (customer_id, order_date)
SELECT c.customer_id
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id
HAVING MAX(order_date) < CURRENT_DATE - INTERVAL '90 days'
OR MAX(order_date) IS NULL;
๐ Question 52: Calculate Average Revenue Per User ARPU
Tables: users (user_id), orders (user_id, amount)
SELECT
ROUND(
SUM(amount) * 1.0 /
COUNT(DISTINCT u.user_id),
2
) AS arpu
FROM users u
LEFT JOIN orders o
ON u.user_id = o.user_id;
๐ Question 53: Find the Fastest Selling Product
Table: inventory (product_id, launch_date)
Table: sales (product_id, sale_date)
Requirement: Find the product with the fewest days from launch to first sale.
WITH first_sale AS (
SELECT
product_id,
MIN(sale_date) AS first_sale_date
FROM sales
GROUP BY product_id
)
SELECT
i.product_id,
(first_sale_date - launch_date) AS days_to_sell
FROM inventory i
JOIN first_sale f
ON i.product_id = f.product_id
ORDER BY days_to_sell
LIMIT 1;
๐ Question 54: Find Customers Who Purchased in Every Quarter
Table: orders (customer_id, order_date)
WITH customer_quarters AS (
SELECT
customer_id,
COUNT(
DISTINCT DATE_TRUNC('quarter', order_date)
) AS quarter_count
FROM orders
GROUP BY customer_id
),
total_quarters AS (
SELECT COUNT(
DISTINCT DATE_TRUNC('quarter', order_date)
) AS total_quarters
FROM orders
)
SELECT customer_id
FROM customer_quarters
CROSS JOIN total_quarters
WHERE quarter_count = total_quarters;
๐ Question 55: Find Revenue Contribution of Top 10 Customers
Table: orders (customer_id, amount)
WITH customer_revenue AS (
SELECT
customer_id,
SUM(amount) AS revenue
FROM orders
GROUP BY customer_id
)
SELECT
SUM(revenue) AS top10_revenue,
ROUND(
100.0 * SUM(revenue) /
(
SELECT SUM(amount)
FROM orders
),
2
) AS contribution_pct
FROM (
SELECT revenue
FROM customer_revenue
ORDER BY revenue DESC
LIMIT 10
) t;
๐ Question 56: Find Products Never Returned
Tables:
products (product_id)
sales (order_id, product_id)
returns (order_id)
SELECT DISTINCT
p.product_id
FROM products p
LEFT JOIN sales s
ON p.product_id = s.product_id
LEFT JOIN returns r
ON s.order_id = r.order_id
WHERE r.order_id IS NULL;
๐ Question 57: Calculate Daily Revenue Growth
Table: sales (sale_date, amount)
WITH daily_sales AS (
SELECT
sale_date,
SUM(amount) AS revenue
FROM sales
GROUP BY sale_date
)
SELECT
sale_date,
revenue,
ROUND(
100.0 *
(
revenue -
LAG(revenue) OVER(
ORDER BY sale_date
)
/
LAG(revenue) OVER(
ORDER BY sale_date
),
2
) AS growth_pct
FROM daily_sales;
๐ Question 58: Find the Most Loyal Customers
Table: orders (customer_id, order_date)
Requirement: Customers who placed orders in the highest number of distinct months.
SELECT
customer_id,
COUNT(
DISTINCT DATE_TRUNC('month', order_date)
) AS active_months
FROM orders
GROUP BY customer_id
ORDER BY active_months DESC
LIMIT 10;
๐ Question 59: Find Products With Zero Sales
Tables:
products (product_id)
sales (product_id)
SELECT
p.product_id
FROM products p
LEFT JOIN sales s
ON p.product_id = s.product_id
WHERE s.product_id IS NULL;
๐ Question 60: Calculate Average Orders Per Customer
Table: orders (customer_id)
SELECT
ROUND(
COUNT(*) * 1.0 /
COUNT(DISTINCT customer_id),
2
) AS avg_orders_per_customer
FROM orders;
โค๏ธ Double Tap For More | 1 117 |
| 13 |  ๐ ๐๐ฅ๐๐ ๐๐ฎ๐๐ฎ ๐๐ป๐ฎ๐น๐๐๐ถ๐ฐ๐ ๐๐ผ๐๐ฟ๐๐ฒ๐ | ๐ก๐ผ ๐๐
๐ฝ๐ฒ๐ฟ๐ถ๐ฒ๐ป๐ฐ๐ฒ ๐ก๐ฒ๐ฒ๐ฑ๐ฒ๐ฑ! ๐
Want to start a career in Data Analytics but don't know where to begin?
These 5 FREE beginner-friendly courses will help you learn the most in-demand data skills and build a strong foundation.
๐ ๐๐ป๐ฟ๐ผ๐น๐น ๐๐ผ๐ฟ ๐๐ฅ๐๐๐:
https://pdlink.in/3SOk64h
๐ Start Learning Today. Build Your Portfolio. Land Your Dream Data Job! | 1 111 |
| 14 | ๐ SQL Scenario Based Interview Questions with Answers: Part-5
๐ Question 41: Find Customers Whose Spending Decreased for 3 Consecutive Monthsย
Table: orders (customer_id, amount, order_date)
WITH monthly_spend AS (
ย ย ย SELECT
ย ย ย ย ย ย ย customer_id,
ย ย ย ย ย ย ย DATE_TRUNC('month', order_date) AS month,
ย ย ย ย ย ย ย SUM(amount) AS revenue
ย ย ย FROM orders
ย ย ย GROUP BY customer_id, DATE_TRUNC('month', order_date)
),
spend_trend AS (
ย ย ย SELECT *,
ย ย ย ย ย ย ย ย ย ย LAG(revenue,1) OVER(PARTITION BY customer_id ORDER BY month) AS prev1,
ย ย ย ย ย ย ย ย ย ย LAG(revenue,2) OVER(PARTITION BY customer_id ORDER BY month) AS prev2
ย ย ย FROM monthly_spend
)
SELECT customer_id, month
FROM spend_trend
WHERE revenue < prev1
ย AND prev1 < prev2;
๐ Question 42: Find the Median Order Amount for Each Monthย
Table: orders (order_id, amount, order_date)
SELECT
ย ย ย DATE_TRUNC('month', order_date) AS month,
ย ย ย PERCENTILE_CONT(0.5)
ย ย ย ย ย ย ย WITHIN GROUP (ORDER BY amount) AS median_order
FROM orders
GROUP BY DATE_TRUNC('month', order_date);
๐ Question 43: Find Customers Who Ordered on Every Weekendย
Table: orders (customer_id, order_date)
SELECT
ย ย ย customer_id
FROM orders
WHERE EXTRACT(DOW FROM order_date) IN (0,6)
GROUP BY customer_id
HAVING COUNT(DISTINCT order_date) >= 8;
๐ Question 44: Find the Top 5% Highest Revenue Customersย
Table: orders (customer_id, amount)
WITH revenue AS (
ย ย ย SELECT
ย ย ย ย ย ย ย customer_id,
ย ย ย ย ย ย ย SUM(amount) AS total_revenue
ย ย ย FROM orders
ย ย ย GROUP BY customer_id
)
SELECT *
FROM (
ย ย ย SELECT *,
ย ย ย ย ย ย ย ย ย ย NTILE(20) OVER (ORDER BY total_revenue DESC) AS bucket
ย ย ย FROM revenue
) t
WHERE bucket = 1;
๐ Question 45: Find the Most Frequently Returned Productย
Tables:
sales (order_id, product_id)
returns (order_id)
SELECT
ย ย ย s.product_id,
ย ย ย COUNT(*) AS return_count
FROM sales s
JOIN returns r
ON s.order_id = r.order_id
GROUP BY s.product_id
ORDER BY return_count DESC
LIMIT 1;
๐ Question 46: Calculate Average Delivery Timeย
Table: deliveries (order_id, order_date, delivery_date)
SELECT
ย ย ย ROUND(
ย ย ย ย ย ย ย AVG(delivery_date - order_date),
ย ย ย ย ย ย ย 2
ย ย ย ) AS avg_delivery_days
FROM deliveries;
๐ Question 47: Find Users Who Logged In Every Day Last Weekย
Table: logins (user_id, login_date)
SELECT
ย ย ย user_id
FROM logins
WHERE login_date >= CURRENT_DATE - INTERVAL '6 day'
GROUP BY user_id
HAVING COUNT(DISTINCT login_date) = 7;
๐ Question 48: Find Products with Revenue Above Category Averageย
Tables:
products (product_id, category)
sales (product_id, amount)
WITH product_revenue AS (
ย ย ย SELECT
ย ย ย ย ย ย ย p.product_id,
ย ย ย ย ย ย ย p.category,
ย ย ย ย ย ย ย SUM(s.amount) AS revenue
ย ย ย FROM products p
ย ย ย JOIN sales s
ย ย ย ย ย ON p.product_id = s.product_id
ย ย ย GROUP BY p.product_id, p.category
)
SELECT *
FROM (
ย ย ย SELECT *,
ย ย ย ย ย ย ย ย ย ย AVG(revenue) OVER (
ย ย ย ย ย ย ย ย ย ย ย ย ย ย PARTITION BY category
ย ย ย ย ย ย ย ย ย ย ) AS category_avg
ย ย ย FROM product_revenue
) t
WHERE revenue > category_avg;
๐ Question 49: Find the Busiest Day of the Weekย
Table: orders (order_date)
SELECT
ย ย ย TO_CHAR(order_date, 'Day') AS weekday,
ย ย ย COUNT(*) AS total_orders
FROM orders
GROUP BY weekday
ORDER BY total_orders DESC
LIMIT 1;
๐ Question 50: Calculate Customer Retention After First Purchase
Table: orders (customer_id, order_date)
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 retention_rate
FROM customer_orders;
๐ฏ Concepts Covered:
โ
Window Functions
โ
Percentiles
โ
NTILE()
โ
Retention Analysis
โ
Revenue Analytics
โ
Delivery KPIs
โ
Customer Behavior Analysis
โ
Advanced Business SQLย
โค๏ธ Double Tap For More | 1 356 |
| 15 |  ๐ ๐๐ฅ๐๐ ๐ง๐ฎ๐๐ฎ ๐๐ฎ๐๐ฎ ๐๐ป๐ฎ๐น๐๐๐ถ๐ฐ๐ ๐ฉ๐ถ๐ฟ๐๐๐ฎ๐น ๐๐ป๐๐ฒ๐ฟ๐ป๐๐ต๐ถ๐ฝ | ๐ช๐ถ๐๐ต ๐๐ฒ๐ฟ๐๐ถ๐ณ๐ถ๐ฐ๐ฎ๐๐ฒ ๐
Here's an amazing opportunity to complete the FREE Tata Data Analytics Virtual Internship and earn a certificate that you can showcase on your Resume and LinkedIn.
โ
100% FREE
โ
Self-Paced & Online
โ
Beginner-Friendly
โ
Certificate on Completion
โ
Real Business Case Studies
โ
Resume & LinkedIn Boost
๐ ๐๐ป๐ฟ๐ผ๐น๐น ๐๐ผ๐ฟ ๐๐ฅ๐๐๐:
https://pdlink.in/4eybW8J
๐ Upskill Today. Build Your Portfolio. Get Career Ready! | 1 433 |
| 16 | SELECT
ย ย ย EXTRACT(HOUR FROM order_timestamp) AS order_hour,
ย ย ย COUNT(*) AS total_orders
FROM orders
GROUP BY order_hour
ORDER BY total_orders DESC
LIMIT 1;
๐ Question 40: Rank Salespersons by Quarterly Revenue
Table: sales salesperson_id, amount, sale_date
WITH quarterly_sales AS (
ย ย ย SELECT
ย ย ย ย ย ย ย salesperson_id,
ย ย ย ย ย ย ย DATE_TRUNC('quarter', sale_date) AS quarter,
ย ย ย ย ย ย ย SUM(amount) AS revenue
ย ย ย FROM sales
ย ย ย GROUP BY 1,2
)
SELECT *,
ย ย ย DENSE_RANK() OVER (
ย ย ย ย ย ย ย PARTITION BY quarter
ย ย ย ย ย ย ย ORDER BY revenue DESC
ย ย ย ) AS sales_rank
FROM quarterly_sales;
Double Tap โค๏ธ For More | 1 583 |
| 17 | SELECT
ย ย ย EXTRACT(HOUR FROM order_timestamp) AS order_hour,
ย ย ย COUNT(*) AS total_orders
FROM orders
GROUP BY order_hour
ORDER BY total_orders DESC
LIMIT 1;
๐ Question 40: Rank Salespersons by Quarterly Revenue
Table: sales salesperson_id, amount, sale_date
WITH quarterly_sales AS (
ย ย ย SELECT
ย ย ย ย ย ย ย salesperson_id,
ย ย ย ย ย ย ย DATE_TRUNC('quarter', sale_date) AS quarter,
ย ย ย ย ย ย ย SUM(amount) AS revenue
ย ย ย FROM sales
ย ย ย GROUP BY 1,2
)
SELECT *,
ย ย ย DENSE_RANK() OVER (
ย ย ย ย ย ย ย PARTITION BY quarter
ย ย ย ย ย ย ย ORDER BY revenue DESC
ย ย ย ) AS sales_rank
FROM quarterly_sales;
Double Tap โค๏ธ For More
-----
1.38 โฝ ยท /balance_help | 1 |
| 18 | ๐ SQL Scenario Based Interview Questions with Answers Part 4
๐ Question 31: Find Customers Who Increased Their Monthly Spending
Table: orders customer_id, amount, order_date
Requirement: Return customers whose spending increased compared to the previous month.
WITH monthly_spend AS (
SELECT
customer_id,
DATE_TRUNC('month', order_date) AS month,
SUM(amount) AS total_spend
FROM orders
GROUP BY customer_id, DATE_TRUNC('month', order_date)
)
SELECT
customer_id,
month,
total_spend
FROM (
SELECT *,
LAG(total_spend) OVER (
PARTITION BY customer_id
ORDER BY month
) AS prev_spend
FROM monthly_spend
) t
WHERE total_spend > prev_spend;
๐ Question 32: Find Products Purchased Together Most Often
Table: order_items order_id, product_id
SELECT
a.product_id AS product_1,
b.product_id AS product_2,
COUNT(*) AS frequency
FROM order_items a
JOIN order_items b
ON a.order_id = b.order_id
AND a.product_id < b.product_id
GROUP BY 1,2
ORDER BY frequency DESC
LIMIT 10;
๐ Question 33: Find Users Active for 7 Consecutive Days
Table: user_activity user_id, activity_date
WITH activity 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 user_activity
)
SELECT
user_id
FROM activity
GROUP BY user_id, grp
HAVING COUNT(*) >= 7;
๐ Question 34: Calculate Repeat Purchase Rate
Table: orders customer_id, order_id
SELECT
ROUND(
100.0 *
COUNT(CASE WHEN order_count > 1 THEN 1 END) /
COUNT(*),
2
) AS repeat_purchase_rate
FROM (
SELECT
customer_id,
COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
) t;
๐ Question 35: Find the Longest Gap Between Two Orders
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 gap_days
FROM orders
)
SELECT
customer_id,
MAX(gap_days) AS longest_gap
FROM gaps
GROUP BY customer_id;
๐ Question 36: Calculate Revenue Lost Due to Churn
Tables: customers customer_id, status, orders customer_id, amount
SELECT
SUM(amount) AS churned_revenue
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id
WHERE c.status = 'Churned';
๐ Question 37: Find the Fastest Growing Product
Table: sales product_id, amount, sale_date
WITH monthly_sales AS (
SELECT
product_id,
DATE_TRUNC('month', sale_date) AS month,
SUM(amount) AS revenue
FROM sales
GROUP BY 1,2
)
SELECT *
FROM (
SELECT *,
revenue -
LAG(revenue) OVER (
PARTITION BY product_id
ORDER BY month
) AS growth
FROM monthly_sales
) t
ORDER BY growth DESC
LIMIT 1;
๐ Question 38: Find Customers Who Purchased Every Product Category
Tables: products product_id, category, orders customer_id, product_id
SELECT
customer_id
FROM orders o
JOIN products p
ON o.product_id = p.product_id
GROUP BY customer_id
HAVING COUNT(DISTINCT category) = (
SELECT COUNT(DISTINCT category)
FROM products
);
๐ Question 39: Find Peak Ordering Hour
Table: orders order_id, order_timestamp | 1 383 |
| 19 |  ๐ ๐ฃ๐๐ ๐ถ๐ ๐ผ๐ณ๐ณ๐ฒ๐ฟ๐ถ๐ป๐ด ๐ฎ ๐๐ฅ๐๐ ๐ฃ๐ผ๐๐ฒ๐ฟ ๐๐ ๐๐ฒ๐ฟ๐๐ถ๐ณ๐ถ๐ฐ๐ฎ๐๐ถ๐ผ๐ป ๐ฃ๐ฟ๐ผ๐ด๐ฟ๐ฎ๐บ
This helps tolearn data visualization, dashboard creation, KPI analysis, and business intelligence skills that companies actively look for.
โ
Free Certificate
โ
Self-Paced Learning
โ
Hands-On Power BI Projects
โ
Beginner Friendly
โ
Resume & LinkedIn Boost
Don't miss this opportunity to add an in-demand skill to your profile and stand out from the crowd! ๐ผ๐ฅ
๐ ๐๐ป๐ฟ๐ผ๐น๐น ๐๐ผ๐ฟ ๐๐ฅ๐๐๐:
https://pdlink.in/4g5sKFa
Share with yours friends who wants to start a career in Data Analytics | 1 323 |
| 20 | ๐ SQL Scenario Based Interview Questions with Answers: Part-3
๐ Question 21: Find the First Purchase Date for Every Customer
Table: orders order_id, customer_id, order_date
SELECT
customer_id,
MIN(order_date) AS first_purchase_date
FROM orders
GROUP BY customer_id;
๐ Question 22: Calculate Customer Lifetime Value (CLV)
Table: orders customer_id, amount
Requirement: Total revenue generated by each customer.
SELECT
customer_id,
SUM(amount) AS lifetime_value
FROM orders
GROUP BY customer_id
ORDER BY lifetime_value DESC;
๐ Question 23: Find the Top 5 Products Contributing 80% of Revenue
Table: sales product_id, amount
WITH product_revenue AS (
SELECT
product_id,
SUM(amount) AS revenue
FROM sales
GROUP BY product_id
),
revenue_rank AS (
SELECT
product_id,
revenue,
SUM(revenue) OVER (ORDER BY revenue DESC) AS running_revenue,
SUM(revenue) OVER () AS total_revenue
FROM product_revenue
)
SELECT
product_id,
revenue,
ROUND(100.0 * running_revenue / total_revenue, 2) AS cumulative_pct
FROM revenue_rank
WHERE running_revenue <= total_revenue * 0.80;
๐ Question 24: Find Customers Who Purchased More Than 3 Different Products
Table: orders customer_id, product_id
SELECT
customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(DISTINCT product_id) > 3;
๐ Question 25: Find the Highest Revenue Order for Each Customer
Table: orders order_id, customer_id, amount
WITH ranked_orders AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY amount DESC
) AS rn
FROM orders
)
SELECT
customer_id,
order_id,
amount
FROM ranked_orders
WHERE rn = 1;
๐ Question 26: Calculate Average Time Between Orders
Table: orders customer_id, order_date
WITH order_gap 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,
AVG(order_date - previous_order) AS avg_days_between_orders
FROM order_gap
WHERE previous_order IS NOT NULL
GROUP BY customer_id;
๐ Question 27: Find Users Who Abandoned Their Cart
Tables: cart user_id, product_id, orders user_id
Requirement: Users who added items to their cart but never completed a purchase.
SELECT DISTINCT
c.user_id
FROM cart c
LEFT JOIN orders o
ON c.user_id = o.user_id
WHERE o.user_id IS NULL;
๐ Question 28: Find Revenue Generated by New vs Returning Customers
Tables: users user_id, signup_date, orders user_id, amount, order_date
SELECT
CASE
WHEN DATE_TRUNC('month', signup_date) =
DATE_TRUNC('month', order_date)
THEN 'New'
ELSE 'Returning'
END AS customer_type,
SUM(amount) AS revenue
FROM users u
JOIN orders o
ON u.user_id = o.user_id
GROUP BY customer_type;
๐ Question 29: Find the Most Frequently Purchased Product Pair
Table: order_items order_id, product_id
SELECT
a.product_id AS product_1,
b.product_id AS product_2,
COUNT(*) AS pair_count
FROM order_items a
JOIN order_items b
ON a.order_id = b.order_id
AND a.product_id < b.product_id
GROUP BY
a.product_id,
b.product_id
ORDER BY pair_count DESC
LIMIT 1;
๐ Question 30: Calculate Revenue Contribution by Region
Tables: customers customer_id, region, orders customer_id, amount
SELECT
c.region,
SUM(o.amount) AS revenue,
ROUND(
100.0 * SUM(o.amount) /
SUM(SUM(o.amount)) OVER (),
2
) AS revenue_share
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY c.region
ORDER BY revenue DESC;
Double Tap โค๏ธ For More | 1 487 |
