Top 10 Advanced SQL Queries for Data Mastery

1. Recursive CTE (Common Table Expressions)
Use a recursive CTE to traverse hierarchical data, such as employees and their managers.

WITH RECURSIVE EmployeeHierarchy AS (
  SELECT employee_id, employee_name, manager_id
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.employee_name, e.manager_id
  FROM employees e
  JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * 
FROM EmployeeHierarchy;

2. Pivoting Data
Turn row data into columns (e.g., show product categories as separate columns).

SELECT *
FROM (
    SELECT TO_CHAR(order_date, 'YYYY-MM') AS month, product_category, sales_amount
    FROM sales
) AS pivot_data
PIVOT (
    SUM(sales_amount)
    FOR product_category IN ('Electronics', 'Clothing', 'Books')
) AS pivoted_sales;

3. Window Functions
Calculate a running total of sales based on order date.

SELECT 
  order_date, 
  sales_amount, 
  SUM(sales_amount) OVER (ORDER BY order_date) AS running_total
FROM sales;

4. Ranking with Window Functions
Rank employees’ salaries within each department.

SELECT 
  department, 
  employee_name, 
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees;

5. Finding Gaps in Sequences
Identify missing values in a sequential dataset (e.g., order numbers).

WITH Sequences AS (
  SELECT MIN(order_number) AS start_seq, MAX(order_number) AS end_seq
  FROM orders
)
SELECT start_seq + 1 AS missing_sequence
FROM Sequences
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.order_number = Sequences.start_seq + 1
);

6. Unpivoting Data
Convert columns into rows to simplify analysis of multiple attributes.

SELECT 
  product_id, 
  attribute_name, 
  attribute_value
FROM products
UNPIVOT (
  attribute_value FOR attribute_name IN (color, size, weight)
) AS unpivoted_data;

7. Finding Consecutive Events
Check for consecutive days/orders for the same product using LAG().

WITH ConsecutiveOrders AS (
  SELECT 
    product_id, 
    order_date,
    LAG(order_date) OVER (PARTITION BY product_id ORDER BY order_date) AS prev_order_date
  FROM orders
)
SELECT product_id, order_date, prev_order_date
FROM ConsecutiveOrders
WHERE order_date - prev_order_date = 1;

8. Aggregation with the FILTER Clause
Calculate selective averages (e.g., only for the Sales department).

SELECT 
  department,
  AVG(salary) FILTER (WHERE department = 'Sales') AS avg_salary_sales
FROM employees
GROUP BY department;

9. JSON Data Extraction
Extract values from JSON columns directly in SQL.

SELECT 
  order_id, 
  customer_id,
  order_details ->> 'product' AS product_name,
  CAST(order_details ->> 'quantity' AS INTEGER) AS quantity
FROM orders;

10. Using Temporary Tables
Create a temporary table for intermediate results, then join it with other tables.

-- Create a temporary table
CREATE TEMPORARY TABLE temp_product_sales AS
SELECT product_id, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY product_id;

-- Use the temp table
SELECT p.product_name, t.total_sales
FROM products p
JOIN temp_product_sales t ON p.product_id = t.product_id;

Why These Matter
Advanced SQL queries let you handle complex data manipulation and analysis tasks with ease. From traversing hierarchical relationships to reshaping data (pivot/unpivot) and working with JSON, these techniques expand your ability to derive insights from relational databases.

Keep practicing these queries to solidify your SQL expertise and make more data-driven decisions!

Here you can find essential SQL Interview Resources👇
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Like this post if you need more 👍❤️

Hope it helps :)

#sql #dataanalyst

SQL (Structured Query Language) is the universal language of databases. Whether you're analyzing sales data, optimizing marketing campaigns, or tracking user behavior, SQL is your go-to tool for:

✅ Accessing and managing data efficiently
✅ Writing queries to extract insights
✅ Building a strong foundation for advanced tools like Python, R, or Power BI
In short, SQL is the bridge between raw data and actionable insights. 🌉

SQL Topics to Learn for Data Analyst/Business Analyst Roles

1. Basic:
* SELECT statements
* WHERE clause
* JOINs (INNER, LEFT, RIGHT, FULL)
* GROUP BY and HAVING
* ORDER BY
* Basic Aggregate Functions (COUNT, SUM, AVG, MIN, MAX)

2. Intermediate:
* Subqueries
* CASE statements
* UNION and UNION ALL
* Common Table Expressions (CTEs)
* Window Functions (ROW_NUMBER, RANK, DENSE_RANK, OVER)
* Data Manipulation (INSERT, UPDATE, DELETE)
* Indexes and Performance Tuning

3. Advanced:
* Advanced Window Functions (LEAD, LAG, NTILE)
* Complex Subqueries and Correlated Subqueries
* Advanced Performance Tuning

SQL is not just a skill—it’s the foundation of your data career. 🌟

Here you can find essential SQL Interview Resources👇
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Like this post if you need more 👍❤️

Hope it helps :)

Preparing for a SQL interview?

Focus on mastering these essential topics:

1. Joins: Get comfortable with inner, left, right, and outer joins.
Knowing when to use what kind of join is important!

2. Window Functions: Understand when to use
ROW_NUMBER, RANK(), DENSE_RANK(), LAG, and LEAD for complex analytical queries.

3. Query Execution Order: Know the sequence from FROM to
ORDER BY. This is crucial for writing efficient, error-free queries.

4. Common Table Expressions (CTEs): Use CTEs to simplify and structure complex queries for better readability.

5. Aggregations & Window Functions: Combine aggregate functions with window functions for in-depth data analysis.

6. Subqueries: Learn how to use subqueries effectively within main SQL statements for complex data manipulations.

7. Handling NULLs: Be adept at managing NULL values to ensure accurate data processing and avoid potential pitfalls.

8. Indexing: Understand how proper indexing can significantly boost query performance.

9. GROUP BY & HAVING: Master grouping data and filtering groups with HAVING to refine your query results.

10. String Manipulation Functions: Get familiar with string functions like CONCAT, SUBSTRING, and REPLACE to handle text data efficiently.

11. Set Operations: Know how to use UNION, INTERSECT, and EXCEPT to combine or compare result sets.

12. Optimizing Queries: Learn techniques to optimize your queries for performance, especially with large datasets.

Here you can find essential SQL Interview Resources👇
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Like this post if you need more 👍❤️

Hope it helps :)

Here are 30 most asked SQL questions to clear your next interview -

➤ 𝗪𝗶𝗻𝗱𝗼𝘄 𝗙𝘂𝗻𝗰𝘁𝗶𝗼𝗻𝘀

1. Find the cumulative sum of sales for each employee.
2. Rank employees based on their sales within their department.
3. Calculate a running total of orders by order date.
4. Identify the top three salaries in each department.
5. Compute the difference between the current and previous month's sales.

➤ 𝗖𝗼𝗺𝗺𝗼𝗻 𝗧𝗮𝗯𝗹𝗲 𝗘𝘅𝗽𝗿𝗲𝘀𝘀𝗶𝗼𝗻𝘀 (𝗖𝗧𝗘)

1. Write a recursive CTE to generate a sequence of numbers from 1 to 100.
2. Use a CTE to find employees who directly and indirectly report to a specific manager.
3. Calculate the factorial of a number using a recursive CTE.
4. Flatten a hierarchical organization chart using a CTE.
5. Use a CTE to calculate year-over-year growth in sales.

➤ 𝗝𝗼𝗶𝗻𝘀 (𝗜𝗻𝗻𝗲𝗿, 𝗢𝘂𝘁𝗲𝗿, 𝗖𝗿𝗼𝘀𝘀, 𝗦𝗲𝗹𝗳)

1. Retrieve a list of customers who have placed orders (Inner Join).
2. Find employees who have not been assigned to any projects (Left Join).
3. Get a list of projects without assigned employees (Right Join).
4. Generate all possible pairs of products (Cross Join).
5. Match employees to themselves to find pairs from the same department (Self Join).

➤ 𝗦𝘂𝗯𝗾𝘂𝗲𝗿𝗶𝗲𝘀

1. Find employees earning more than the average salary in their department.
2. Retrieve customers who have placed more than 5 orders.
3. List products that have never been ordered.
4. Identify the second highest salary in the company using a subquery.
5. Find departments where all employees earn above a specific threshold.

➤ 𝗔𝗴𝗴𝗿𝗲𝗴𝗮𝘁𝗲 𝗙𝘂𝗻𝗰𝘁𝗶𝗼𝗻𝘀

1. Calculate the total revenue generated by each product.
2. Find the average salary in each department.
3. Count the number of orders placed by each customer.
4. Find the maximum and minimum sales for each region.
5. Calculate the standard deviation of employee salaries.

➤ 𝗜𝗻𝗱𝗲𝘅𝗶𝗻𝗴 𝗮𝗻𝗱 𝗣𝗲𝗿𝗳𝗼𝗿𝗺𝗮𝗻𝗰𝗲

1. Identify queries that would benefit from indexing.
2. Compare execution plans with and without an index on a specific column.
3. Check which indexes exist on a table.
4. Optimize a slow query using indexing.
5. Write a query to update a column using an indexed lookup.

Here you can find essential SQL Interview Resources👇
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Like this post if you need more 👍❤️

Hope it helps :)

Here are some tricky SQL interview questions!

1. Find the second-highest salary in a table without using LIMIT or TOP.

2. Write a SQL query to find all employees who earn more than their managers.

3. Find the duplicate rows in a table without using GROUP BY.

4. Write a SQL query to find the top 10% of earners in a table.

5. Find the cumulative sum of a column in a table.

6. Write a SQL query to find all employees who have never taken a leave.

7. Find the difference between the current row and the next row in a table.

8. Write a SQL query to find all departments with more than one employee.

9. Find the maximum value of a column for each group without using GROUP BY.

10. Write a SQL query to find all employees who have taken more than 3 leaves in a month.

These questions are designed to test your SQL skills, including your ability to write efficient queries, think creatively, and solve complex problems.

Here are the answers to these questions:

1. SELECT MAX(salary) FROM table WHERE salary NOT IN (SELECT MAX(salary) FROM table)

2. SELECT e1.* FROM employees e1 JOIN employees e2 ON e1.manager_id = (link unavailable) WHERE e1.salary > e2.salary

3. SELECT * FROM table WHERE rowid IN (SELECT rowid FROM table GROUP BY column HAVING COUNT(*) > 1)

4. SELECT * FROM table WHERE salary > (SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY salary) FROM table)

5. SELECT column, SUM(column) OVER (ORDER BY rowid) FROM table

6. SELECT * FROM employees WHERE id NOT IN (SELECT employee_id FROM leaves)

7. SELECT *, column - LEAD(column) OVER (ORDER BY rowid) FROM table

8. SELECT department FROM employees GROUP BY department HAVING COUNT(*) > 1

9. SELECT MAX(column) FROM table WHERE column NOT IN (SELECT MAX(column) FROM table GROUP BY group_column)

Here you can find essential SQL Interview Resources👇
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Like this post if you need more 👍❤️

Hope it helps :)

𝗧𝗼𝗽 𝗠𝗡𝗖𝘀 𝗛𝗶𝗿𝗶𝗻𝗴 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘀𝘁𝘀 😍

- Capgemini 
- Infosys
- KPMG
- Genpact
- JP Morgan

Qualification :- Any Graduate 

𝐑𝐞𝐠𝐢𝐬𝐭𝐞𝐫 & 𝐔𝐩𝐥𝐨𝐚𝐝 𝐘𝐨𝐮𝐫 𝐑𝐞𝐬𝐮𝐦𝐞👇:-  

https://bit.ly/3ZI20AY

Enter your experience & Complete The Registration Process

Select the company name & Apply for jobs

Netflix Analytics Engineer Interview Experience:


SQL Questions:

1️⃣ SQL Question 1: Identify VIP Users for Netflix

Question: To better cater to its most dedicated users, Netflix would like to identify its “VIP users” - those who are most active in terms of the number of hours of content they watch. Write a SQL query that will retrieve the top 10 users with the most watched hours in the last month.

Tables:
• users table: user_id (integer), sign_up_date (date), subscription_type (text)
• watching_activity table: activity_id (integer), user_id (integer), date_time (timestamp), show_id (integer), hours_watched (float)

2️⃣ SQL Question 2: Analyzing Ratings For Netflix Shows

Question: Given a table of user ratings for Netflix shows, calculate the average rating for each show within a given month. Assume that there is a column for user_id, show_id, rating (out of 5 stars), and date of review. Order the results by month and then by average rating (descending order).

Tables:
• show_reviews table: review_id (integer), user_id (integer), review_date (timestamp), show_id (integer), stars (integer)

3️⃣ SQL Question 3: What does EXCEPT / MINUS SQL commands do?

Question: Explain the purpose and usage of the EXCEPT (or MINUS in some SQL dialects) SQL commands.

4️⃣ SQL Question 4: Filter Netflix Users Based on Viewing History and Subscription Status

Question: You are given a database of Netflix’s user viewing history and their current subscription status. Write a SQL query to find all active customers who watched more than 10 episodes of a show called “Stranger Things” in the last 30 days.

Tables:
• users table: user_id (integer), active (boolean)
• viewing_history table: user_id (integer), show_id (integer), episode_id (integer), watch_date (date)
• shows table: show_id (integer), show_name (text)

5️⃣ SQL Question 5: What does it mean to denormalize a database?

Question: Explain the concept and implications of denormalizing a database.

6️⃣ SQL Question 6: Filter and Match Customer’s Viewing Records

Question: As a data analyst at Netflix, you are asked to analyze the customer’s viewing records. You confirmed that Netflix is especially interested in customers who have been continuously watching a particular genre - ‘Documentary’ over the last month. The task is to find the name and email of those customers who have viewed more than five ‘Documentary’ movies within the last month. ‘Documentary’ could be a part of a broader genre category in the genre field (for example, ‘Documentary, History’). Therefore, the matching pattern could occur anywhere within the string.

Tables:
• movies table: movie_id (integer), title (text), genre (text), release_year (integer)
• customer table: user_id (integer), name (text), email (text), last_movie_watched (integer), date_watched (date)

Here you can find essential SQL Interview Resources👇
https://t.iss.one/mysqldata

Like this post if you need more 👍❤️

Hope it helps :)

SQL is easy to learn, but difficult to master.

Here are 5 hacks to level up your SQL 👇

1. Know complex joins
2. Master Window functions
3. Explore alternative solutions
4. Master query optimization
5. Get familiar with ETL

———

𝘉𝘵𝘸, 𝘵𝘩𝘦𝘳𝘦 𝘢𝘳𝘦 𝘱𝘳𝘢𝘤𝘵𝘪𝘤𝘦 𝘱𝘳𝘰𝘣𝘭𝘦𝘮𝘴 𝘪𝘯 𝘵𝘩𝘦 𝘤𝘢𝘳𝘰𝘶𝘴𝘦𝘭.

𝟭/ 𝗞𝗻𝗼𝘄 𝗰𝗼𝗺𝗽𝗹𝗲𝘅 𝗷𝗼𝗶𝗻𝘀

LEFT JOIN, RIGHT JOIN, INNER JOIN, OUTER JOIN — these are easy.

But SQL gets really powerful, when you know
↳ Anti Joins
↳ Self Joins
↳ Cartesian Joins
↳ Multi-Table Joins

𝟮/ 𝗠𝗮𝘀𝘁𝗲𝗿 𝗪𝗶𝗻𝗱𝗼𝘄 𝗳𝘂𝗻𝗰𝘁𝗶𝗼𝗻𝘀

Window functions = flexible, effective, and essential.

They give you Python-like versatility in SQL. 𝘚𝘶𝘱𝘦𝘳 𝘤𝘰𝘰𝘭.

𝟯/ 𝗘𝘅𝗽𝗹𝗼𝗿𝗲 𝗮𝗹𝘁𝗲𝗿𝗻𝗮𝘁𝗶𝘃𝗲 𝘀𝗼𝗹𝘂𝘁𝗶𝗼𝗻𝘀

In SQL, there’s rarely one “right” way to solve a problem.

By exploring alternative approaches, you develop flexibility in thinking AND learn about trade-offs.

𝟰/ 𝗠𝗮𝘀𝘁𝗲𝗿 𝗾𝘂𝗲𝗿𝘆 𝗼𝗽𝘁𝗶𝗺𝗶𝘇𝗮𝘁𝗶𝗼𝗻

Inefficient queries overload systems, cost money and waste time.

3 (super quick) tips on optimizing queries:
1. Use indexes effectively
2. Analyze execution plans
3. Reduce unnecessary operations

𝟱/ 𝗚𝗲𝘁 𝗳𝗮𝗺𝗶𝗹𝗶𝗮𝗿 𝘄𝗶𝘁𝗵 𝗘𝗧𝗟

ETL is the backbone of moving and preparing data.

↳ Extract: Pull data from various sources
↳ Transform: Clean, filter, and reformat the data
↳ Load: Store the cleaned data in a data warehouse

Here you can find essential SQL Interview Resources👇
https://t.iss.one/mysqldata

Like this post if you need more 👍❤️

Hope it helps :)

35 Most Common SQL Interview Questions 👇👇

1.) Explain order of execution of SQL.
2.) What is difference between where and having?
3.) What is the use of group by?
4.) Explain all types of joins in SQL?
5.) What are triggers in SQL?
6.) What is stored procedure in SQL
7.) Explain all types of window functions?
(Mainly rank, row_num, dense_rank, lead & lag)
8.) What is difference between Delete and Truncate?
9.) What is difference between DML, DDL and DCL?
10.) What are aggregate function and when do we use them? explain with few example.
11.) Which is faster between CTE and Subquery?
12.) What are constraints and types of Constraints?
13.) Types of Keys?
14.) Different types of Operators ?
15.) Difference between Group By and Where?
16.) What are Views?
17.) What are different types of constraints?
18.) What is difference between varchar and nvarchar?
19.) Similar for char and nchar?
20.) What are index and their types?
21.) What is an index? Explain its different types.
22.) List the different types of relationships in SQL.
23.) Differentiate between UNION and UNION ALL.
24.) How many types of clauses in SQL?
25.) What is the difference between UNION and UNION ALL in SQL?
26.) What are the various types of relationships in SQL?
27.) Difference between Primary Key and Secondary Key?
28.) What is the difference between where and having?
29.) Find the second highest salary of an employee?
30.) Write retention query in SQL?
31.) Write year-on-year growth in SQL?
32.) Write a query for cummulative sum in SQL?
33.) Difference between Function and Store procedure ?
34.) Do we use variable in views?
35.) What are the limitations of views?

Here you can find essential SQL Interview Resources👇
https://t.iss.one/mysqldata

Like this post if you need more 👍❤️

Hope it helps :)

SQL, or Structured Query Language, is a domain-specific language used to manage and manipulate relational databases. Here's a brief A-Z overview by @sqlanalyst

A - Aggregate Functions: Functions like COUNT, SUM, AVG, MIN, and MAX used to perform operations on data in a database.

B - BETWEEN: A SQL operator used to filter results within a specific range.

C - CREATE TABLE: SQL statement for creating a new table in a database.

D - DELETE: SQL statement used to delete records from a table.

E - EXISTS: SQL operator used in a subquery to test if a specified condition exists.

F - FOREIGN KEY: A field in a database table that is a primary key in another table, establishing a link between the two tables.

G - GROUP BY: SQL clause used to group rows that have the same values in specified columns.

H - HAVING: SQL clause used in combination with GROUP BY to filter the results.

I - INNER JOIN: SQL clause used to combine rows from two or more tables based on a related column between them.

J - JOIN: Combines rows from two or more tables based on a related column.

K - KEY: A field or set of fields in a database table that uniquely identifies each record.

L - LIKE: SQL operator used in a WHERE clause to search for a specified pattern in a column.

M - MODIFY: SQL command used to modify an existing database table.

N - NULL: Represents missing or undefined data in a database.

O - ORDER BY: SQL clause used to sort the result set in ascending or descending order.

P - PRIMARY KEY: A field in a table that uniquely identifies each record in that table.

Q - QUERY: A request for data from a database using SQL.

R - ROLLBACK: SQL command used to undo transactions that have not been saved to the database.

S - SELECT: SQL statement used to query the database and retrieve data.

T - TRUNCATE: SQL command used to delete all records from a table without logging individual row deletions.

U - UPDATE: SQL statement used to modify the existing records in a table.

V - VIEW: A virtual table based on the result of a SELECT query.

W - WHERE: SQL clause used to filter the results of a query based on a specified condition.

X - (E)XISTS: Used in conjunction with SELECT to test the existence of rows returned by a subquery.

Z - ZERO: Represents the absence of a value in numeric fields or the initial state of boolean fields.

Here you can find essential SQL Interview Resources👇
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Like this post if you need more 👍❤️

Hope it helps :)