SQL Programming Resources

GigaChat 3.5 Ultra Publicly Released — The New Generation of the Flagship Model

The GigaChat team has released GigaChat 3.5 Ultra as open source—a new 432B model under the MIT license. This is the first open-source hybrid of GatedDeltaNet and MLA scaled to hundreds of billions of parameters, featuring a proprietary training recipe we refined through more than 1,500 experiments. The model has grown in terms of code, mathematics, agent scenarios, and application domains—yet it’s 40% smaller than GigaChat 3.1 Ultra.

What’s inside:

🔘A proprietary hybrid MLA + Gated DeltaNet architecture with a dedicated stabilization framework, without which this hybrid setup would not train reliably at this scale;
🔘 Gated Attention: the model can locally down-weight overly strong signals from the attention layer;
🔘GatedNorm: normalization with an explicit gate that controls signal magnitude across features;
🔘Approximately 4x lower KV cache per token: with the same memory budget, the model can support 2.14x longer context and deliver a 20% throughput increase under load;
🔘Two MTP heads, enabling up to 2.2x faster generation;
🔘FP8 across all training stages with no quality degradation compared with bf16, enabled by custom Triton and CUDA kernels;
🔘A new online RL stage after SFT and DPO.

Results:

🔘 GigaChat-3.5-Ultra-Base outperforms DeepSeek V3.2 Exp Base and DeepSeek V4 Flash Base on average across a set of general, math, and code benchmarks:
🔘 GigaChat-3.5-Ultra-Instruct is comparable to DeepSeek V3.2 in terms of average score, despite having half the size;
🔘 According to the MiniMax-M2.7 LLM judge, the average win rate against GigaChat 3.1 Ultra is 75.9%, and against GPT-5 is 68.7%.

The entire stack — data (our own LLM-filtered Common Crawl, 600+ programming languages in the code), architecture, training methodology, and infrastructure — was built end-to-end by GigaChat team.

➡️ HuggingFace

🚀 SQL Project Series #6: Banking Transaction Analysis 🏦

Learn how banks use SQL to analyze customer transactions, monitor account activity, detect fraud, and generate business insights.

🎯 Business Objectives

✅ Analyze customer transactions

✅ Calculate account balances

✅ Identify high-value customers

✅ Detect suspicious transactions

✅ Monitor transaction trends

✅ Analyze deposits vs withdrawals

✅ Measure customer activity

✅ Identify dormant accounts

✅ Generate banking KPIs

📂 Step 1: Create Database

CREATE DATABASE banking_db;

USE banking_db;

📂 Step 2: Create Customers Table

CREATE TABLE customers (

customer_id INT PRIMARY KEY,

customer_name VARCHAR(100),

gender VARCHAR(10),

city VARCHAR(50),

account_open_date DATE

);

📂 Step 3: Create Accounts Table

CREATE TABLE accounts (

account_id INT PRIMARY KEY,

customer_id INT,

account_type VARCHAR(30),

branch_name VARCHAR(100),

opening_balance DECIMAL(12,2),

FOREIGN KEY (customer_id)

REFERENCES customers(customer_id)

);

📂 Step 4: Create Transactions Table

CREATE TABLE transactions (

transaction_id INT PRIMARY KEY,

account_id INT,

transaction_date TIMESTAMP,

transaction_type VARCHAR(20),

amount DECIMAL(12,2),

FOREIGN KEY (account_id)

REFERENCES accounts(account_id)

);

📂 Step 5: Insert Sample Customers

INSERT INTO customers VALUES

(1,'Rahul Sharma','Male','Mumbai','2023-01-10'),

(2,'Priya Verma','Female','Delhi','2023-02-18'),

(3,'Amit Patel','Male','Pune','2023-04-12'),

(4,'Sneha Joshi','Female','Bangalore','2023-05-22'),

(5,'Rohan Gupta','Male','Hyderabad','2023-08-01');

📂 Step 6: Insert Sample Accounts

INSERT INTO accounts VALUES

(101,1,'Savings','Mumbai',50000),

(102,2,'Savings','Delhi',35000),

(103,3,'Current','Pune',100000),

(104,4,'Savings','Bangalore',45000),

(105,5,'Current','Hyderabad',75000);

📂 Step 7: Insert Sample Transactions

INSERT INTO transactions VALUES

(1001,101,'2025-01-05 10:15:00','Deposit',15000),

(1002,101,'2025-01-06 12:30:00','Withdrawal',5000),

(1003,102,'2025-01-07 14:20:00','Deposit',25000),

(1004,103,'2025-01-08 16:10:00','Withdrawal',30000),

(1005,104,'2025-01-09 09:45:00','Deposit',12000),

(1006,105,'2025-01-10 11:00:00','Withdrawal',10000);

🧠 SQL Concepts You'll Practice

✔ DDL Commands

✔ DML Commands

✔ Primary & Foreign Keys

✔ Joins

✔ Aggregate Functions

✔ CASE WHEN

✔ GROUP BY

✔ HAVING

✔ Window Functions

✔ CTEs

✔ Date & Time Functions

📊 Business KPIs You Can Build

📈 Total Transaction Amount

📈 Total Deposits

📈 Total Withdrawals

📈 Net Cash Flow

📈 Current Account Balance

📈 Average Transaction Amount

📈 Daily Transaction Volume

📈 Monthly Transaction Trend

📈 Branch-wise Transactions

📈 Account Type Analysis

📈 Top Customers by Transaction Value

📈 Most Active Customers

📈 Dormant Accounts

📈 High-Value Transactions

📈 Deposit vs Withdrawal Ratio

📈 Fraud Detection Alerts

📈 Average Balance by Branch

📈 Customer Growth

📈 Transaction Success Rate

📈 Peak Transaction Hours

💡 Double Tap ❤️ For More

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

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

🚀 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

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

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;

𝗞𝗶𝗰𝗸𝘀𝘁𝗮𝗿𝘁 𝗬𝗼𝘂𝗿 𝗔𝗜 𝗝𝗼𝘂𝗿𝗻𝗲𝘆 | 𝟱 𝗠𝘂𝘀𝘁-𝗪𝗮𝘁𝗰𝗵 𝗙𝗥𝗘𝗘 𝗩𝗶𝗱𝗲𝗼𝘀 🚀

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.

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

𝗙𝗥𝗘𝗘 𝗣𝘆𝘁𝗵𝗼𝗻 𝗣𝗿𝗼𝗴𝗿𝗮𝗺𝗺𝗶𝗻𝗴 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 | 𝟰 𝗠𝘂𝘀𝘁-𝗧𝗮𝗸𝗲 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 🚀

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

🚀 𝗙𝗥𝗘𝗘 𝗧𝗖𝗦 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 | 𝗕𝗼𝗼𝘀𝘁 𝗬𝗼𝘂𝗿 𝗖𝗮𝗿𝗲𝗲𝗿🎓

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.

🚨 SQL Fact Most Beginners Learn Too Late!

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.

💡 When 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!

📊 𝗕𝗲𝘀𝘁 𝗬𝗼𝘂𝗧𝘂𝗯𝗲 𝗖𝗵𝗮𝗻𝗻𝗲𝗹𝘀 𝘁𝗼 𝗟𝗲𝗮𝗿𝗻 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 🚀

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

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!

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

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.

🚀 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

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

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