Top Tools to learn Data Engineering

Join for more data engineering resources: https://t.iss.one/sql_engineer

The way you WRITE an SQL query differs from the way SQL PROCESSES it DataAnalytics

Your query might look like this:

1. SELECT
2. FROM
3. WHERE
4. GROUP BY
5. HAVING
6. ORDER BY

But SQL executes queries in a different order:

FROM (identifies table)
WHERE (filters rows)
GROUP BY (groups rows)
HAVING (filters grouped rows)
SELECT (chooses columns to display)
ORDER BY (sorts final output)

Why SQL is a Must-Have Skill?

If you're working with data, mastering SQL is non-negotiable! It’s the backbone of handling and making sense of vast datasets in any industry.

◆ Data at Your Fingertips
Effortlessly organize, retrieve, and manage large datasets to make informed decisions faster.

◆ Stay Organized
Use primary and foreign keys to keep your data accurate and connected across tables.

◆ Unlock Insights
Combine data from multiple sources and uncover trends using SQL's powerful query capabilities.

◆ Efficiency Matters
Optimize your databases with normalization and avoid unnecessary redundancy.

◆ Advanced Tools
From ACID transactions to optimizing with DELETE vs TRUNCATE, SQL makes sure your data is consistent and secure.

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

Like this post if you need more 👍❤️

Hope it helps :)

SQL Zero to Hero 💪

SQL From Basic to Advanced level

Basic SQL is ONLY 7 commands:
- SELECT
- FROM
- WHERE (also use SQL comparison operators such as =, <=, >=, <> etc.)
- ORDER BY
- Aggregate functions such as SUM, AVERAGE, COUNT etc.
- GROUP BY
- CREATE, INSERT, DELETE, etc.
You can do all this in just one morning.

Once you know these, take the next step and learn commands like:
- LEFT JOIN
- INNER JOIN
- LIKE
- IN
- CASE WHEN
- HAVING (undertstand how it's different from GROUP BY)
- UNION ALL
This should take another day.

Once both basic and intermediate are done, start learning more advanced SQL concepts such as:
- Subqueries (when to use subqueries vs CTE?)
- CTEs (WITH AS)
- Stored Procedures
- Triggers
- Window functions (LEAD, LAG, PARTITION BY, RANK, DENSE RANK)
These can be done in a couple of days.
Learning these concepts is NOT hard at all

- what takes time is practice and knowing what command to use when. How do you master that?
- First, create a basic SQL project
- Then, work on an intermediate SQL project (search online) -

Lastly, create something advanced on SQL with many CTEs, subqueries, stored procedures and triggers etc.

This is ALL you need to become a badass in SQL, and trust me when I say this, it is not rocket science. It's just logic.

Remember that practice is the key here. It will be more clear and perfect with the continous practice

SQL Interview Resources👇
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Like this post if you need more 👍❤️

Hope it helps :)

5 frequently Asked SQL Interview Questions with Answers in Data Engineering interviews:
𝐃𝐢𝐟𝐟𝐢𝐜𝐮𝐥𝐭𝐲 - 𝐌𝐞𝐝𝐢𝐮𝐦

⚫️Determine the Top 5 Products with the Highest Revenue in Each Category.
Schema: Products (ProductID, Name, CategoryID), Sales (SaleID, ProductID, Amount)

WITH ProductRevenue AS (
SELECT p.ProductID,
p.Name,
p.CategoryID,
SUM(s.Amount) AS TotalRevenue,
RANK() OVER (PARTITION BY p.CategoryID ORDER BY SUM(s.Amount) DESC) AS RevenueRank
FROM Products p
JOIN Sales s ON p.ProductID = s.ProductID
GROUP BY p.ProductID, p.Name, p.CategoryID
)
SELECT ProductID, Name, CategoryID, TotalRevenue
FROM ProductRevenue
WHERE RevenueRank <= 5;

⚫️ Identify Employees with Increasing Sales for Four Consecutive Quarters.
Schema: Sales (EmployeeID, SaleDate, Amount)

WITH QuarterlySales AS (
SELECT EmployeeID,
DATE_TRUNC('quarter', SaleDate) AS Quarter,
SUM(Amount) AS QuarterlyAmount
FROM Sales
GROUP BY EmployeeID, DATE_TRUNC('quarter', SaleDate)
),
SalesTrend AS (
SELECT EmployeeID,
Quarter,
QuarterlyAmount,
LAG(QuarterlyAmount, 1) OVER (PARTITION BY EmployeeID ORDER BY Quarter) AS PrevQuarter1,
LAG(QuarterlyAmount, 2) OVER (PARTITION BY EmployeeID ORDER BY Quarter) AS PrevQuarter2,
LAG(QuarterlyAmount, 3) OVER (PARTITION BY EmployeeID ORDER BY Quarter) AS PrevQuarter3
FROM QuarterlySales
)
SELECT EmployeeID, Quarter, QuarterlyAmount
FROM SalesTrend
WHERE QuarterlyAmount > PrevQuarter1 AND PrevQuarter1 > PrevQuarter2 AND PrevQuarter2 > PrevQuarter3;

⚫️ List Customers Who Made Purchases in Each of the Last Three Years.
Schema: Orders (OrderID, CustomerID, OrderDate)

WITH YearlyOrders AS (
SELECT CustomerID,
EXTRACT(YEAR FROM OrderDate) AS OrderYear
FROM Orders
GROUP BY CustomerID, EXTRACT(YEAR FROM OrderDate)
),
RecentYears AS (
SELECT DISTINCT OrderYear
FROM Orders
WHERE OrderDate >= CURRENT_DATE - INTERVAL '3 years'
),
CustomerYearlyOrders AS (
SELECT CustomerID,
COUNT(DISTINCT OrderYear) AS YearCount
FROM YearlyOrders
WHERE OrderYear IN (SELECT OrderYear FROM RecentYears)
GROUP BY CustomerID
)
SELECT CustomerID
FROM CustomerYearlyOrders
WHERE YearCount = 3;


⚫️ Find the Third Lowest Price for Each Product Category.
Schema: Products (ProductID, Name, CategoryID, Price)

WITH RankedPrices AS (
SELECT CategoryID,
Price,
DENSE_RANK() OVER (PARTITION BY CategoryID ORDER BY Price ASC) AS PriceRank
FROM Products
)
SELECT CategoryID, Price
FROM RankedPrices
WHERE PriceRank = 3;

⚫️ Identify Products with Total Sales Exceeding a Specified Threshold Over the Last 30 Days.
Schema: Sales (SaleID, ProductID, SaleDate, Amount)

WITH RecentSales AS (
SELECT ProductID,
SUM(Amount) AS TotalSales
FROM Sales
WHERE SaleDate >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY ProductID
)
SELECT ProductID, TotalSales
FROM RecentSales
WHERE TotalSales > 200;

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

Like this post if you need more 👍❤️

Hope it helps :)

Why SQL Query Optimization Matters More Than You Think

Have you ever noticed your SQL queries dragging their feet, taking forever to return results? It’s frustrating, right? The truth is that optimizing your SQL queries is essential if you want your applications to run smoothly and efficiently.

Here’s why SQL optimization should be on your radar:

→ 𝗦𝗽𝗲𝗲𝗱 𝗠𝗮𝘁𝘁𝗲𝗿𝘀: Did you know that optimized SQL queries can run up to 10 times faster? That means getting the data you need in seconds instead of minutes. Imagine how much more you could get done with that kind of speed!

→ 𝗦𝗮𝘃𝗲 𝗼𝗻 𝗖𝗼𝘀𝘁𝘀: Every second your query runs, it’s using up resources like CPU and memory. By optimizing your queries, you can cut down on resource usage, which can save you a lot of money—especially if you’re working in the cloud where every bit of processing power is billed.

→ 𝗦𝘁𝗮𝘆 𝗦𝗰𝗮𝗹𝗮𝗯𝗹𝗲: Your data isn’t getting any smaller, and as it grows, so does the load on your database. Optimization ensures that as your datasets grow, your queries can handle the increased workload without slowing down. This is crucial if you want to keep everything running smoothly as your business scales.

→ 𝗛𝗮𝗽𝗽𝘆 𝗨𝘀𝗲𝗿𝘀: No one likes waiting, especially not your users. Faster queries mean a better experience for everyone. When your application is responsive, it keeps users engaged and happy, which is a big win.

𝗛𝗲𝗿𝗲’𝘀 𝘁𝗵𝗲 𝗶𝗺𝗽𝗮𝗰𝘁 𝗼𝗳 𝗴𝗲𝘁𝘁𝗶𝗻𝗴 𝗶𝘁 𝗿𝗶𝗴𝗵𝘁:

→ Businesses that focus on optimizing their SQL queries often see a 30-50% improvement in application performance.

→ By just tweaking the slowest 10% of your queries, you can slash your database resource usage by up to 50%.

→ Proper indexing and smart query design can turn minutes into seconds, which is a game-changer for user satisfaction and productivity.

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

Like this post if you need more 👍❤️

Hope it helps :)

How to optimize SQL code:

𝟭) 𝗢𝗻𝗹𝘆 𝘀𝗲𝗹𝗲𝗰𝘁 𝘁𝗵𝗲 𝗻𝗲𝗰𝗲𝘀𝘀𝗮𝗿𝘆 𝗰𝗼𝗹𝘂𝗺𝗻𝘀

This tip is specially important when you work with large tables, running window functions, or multiple joins.

DON'T: Select all the columns from the table
DOs: Select only the columns you need!

𝟮) 𝗔𝗹𝘄𝗮𝘆𝘀 𝗳𝗶𝗹𝘁𝗲𝗿 𝗮𝘀 𝘀𝗼𝗼𝗻 𝗮𝘀 𝘆𝗼𝘂 𝗰𝗮𝗻

If you only need certain rows from a table, apply the filters as early as you can.

Please, don't wait until you do all the CTEs, then all the joins, and realize you need to apply some filters… Your company will thank you for that.

𝟯) 𝗝𝗼𝗶𝗻 𝘆𝗼𝘂𝗿 𝗹𝗮𝗿𝗴𝗲𝘀𝘁 𝘁𝗮𝗯𝗹𝗲 𝗮𝘁 𝘁𝗵𝗲 𝗲𝗻𝗱 (𝗶𝗳 𝘆𝗼𝘂 𝗰𝗮𝗻!)

We've all experienced it. But when you have your biggest table as the base, and joining it to other tables it will result in a lot of scanning.

Look at these 2 queries:

𝟭) 𝗦𝗘𝗟𝗘𝗖𝗧 * 𝗙𝗥𝗢𝗠 𝗯𝗶𝗴_𝘁𝗮𝗯𝗹𝗲 𝗜𝗡𝗡𝗘𝗥 𝗝𝗢𝗜𝗡 𝘁𝗮𝗯𝗹𝗲_𝟭 𝗢𝗡 𝘅𝘅𝘅

vs.

𝟮) 𝗦𝗘𝗟𝗘𝗖𝗧 * 𝗙𝗥𝗢𝗠 𝘁𝗮𝗯𝗹𝗲_𝟭 𝗜𝗡𝗡𝗘𝗥 𝗝𝗢𝗜𝗡 𝗯𝗶𝗴_𝘁𝗮𝗯𝗹𝗲 𝗢𝗡 𝘅𝘅𝘅

The second query will be significantly lower in computing power, so make sure to always start with the smallest table as you base (the best you can!)

𝟰) 𝗕𝗲 𝗚𝗲𝗻𝘁𝗹𝗲 𝘄𝗶𝘁𝗵 𝘄𝗶𝗻𝗱𝗼𝘄 𝗳𝘂𝗻𝗰𝘁𝗶𝗼𝗻𝘀

I've seen many people saying use window function for your queries. Well, I don't think it's a good idea.

Yes, they are powerful but they are also resource-intensive, so use them only if necessary.

And ask yourself how much data it needs to process!

𝟱) 𝗠𝗼𝗿𝗲 𝗖𝗧𝗘𝘀 𝗹𝗲𝘀𝘀 𝘀𝘂𝗯𝗾𝘂𝗲𝗿𝗶𝗲𝘀

Think about your audience. When you are writing a huge query with sub-queries, it is (very!) hard for people to navigate.

Think of replacing them with CTE - they will thank you for that!
And it will also be easier for you to debug and optimize (if needed)

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

Like this post if you need more 👍❤️

Hope it helps :)

SQL Commands Cheatsheet 👆

50 interview SQL questions, including both technical and non-technical questions, along with their answers PART-1

1. What is SQL?
   - Answer: SQL (Structured Query Language) is a standard programming language specifically designed for managing and manipulating relational databases.

2. What are the different types of SQL statements?
   - Answer: SQL statements can be classified into DDL (Data Definition Language), DML (Data Manipulation Language), DCL (Data Control Language), and TCL (Transaction Control Language).

3. What is a primary key?
   - Answer: A primary key is a field (or combination of fields) in a table that uniquely identifies each row/record in that table.

4. What is a foreign key?
   - Answer: A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table or the same table. It establishes a link between the data in two tables.

5. What are joins? Explain different types of joins.
   - Answer: A join is an SQL operation for combining records from two or more tables. 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).

6. What is normalization?
   - Answer: Normalization is the process of organizing data to reduce redundancy and improve data integrity. This typically involves dividing a database into two or more tables and defining relationships between them.

7. What is denormalization?
   - Answer: Denormalization is the process of combining normalized tables into fewer tables to improve database read performance, sometimes at the expense of write performance and data integrity.

8. What is stored procedure?
   - Answer: A stored procedure is a prepared SQL code that you can save and reuse. So, if you have an SQL query that you write frequently, you can save it as a stored procedure and then call it to execute it.

9. What is an index?
   - Answer: An index is a database object that improves the speed of data retrieval operations on a table at the cost of additional storage and maintenance overhead.

10. What is a view in SQL?
    - Answer: A view is a virtual table based on the result set of an SQL query. It contains rows and columns, just like a real table, but does not physically store the data.

11. What is a subquery?
    - Answer: A subquery is an SQL query nested inside a larger query. It is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

12. What are aggregate functions in SQL?
    - Answer: Aggregate functions perform a calculation on a set of values and return a single value. Examples include COUNT, SUM, AVG (average), MIN (minimum), and MAX (maximum).

13. Difference between DELETE and TRUNCATE?
    - Answer: DELETE removes rows one at a time and logs each delete, while TRUNCATE removes all rows in a table without logging individual row deletions. TRUNCATE is faster but cannot be rolled back.

14. What is a UNION in SQL?
    - Answer: UNION is an operator used to combine the result sets of two or more SELECT statements. It removes duplicate rows between the various SELECT statements.

15. What is a cursor in SQL?
    - Answer: A cursor is a database object used to retrieve, manipulate, and navigate through a result set one row at a time.

16. What is trigger in SQL?
    - Answer: A trigger is a set of SQL statements that automatically execute or "trigger" when certain events occur in a database, such as INSERT, UPDATE, or DELETE.

17. Difference between clustered and non-clustered indexes?
    - Answer: A clustered index determines the physical order of data in a table and can only be one per table. A non-clustered index, on the other hand, creates a logical order and can be many per table.

18. Explain the term ACID.
    - Answer: ACID stands for Atomicity, Consistency, Isolation, and Durability.

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

Like this post if you need more 👍❤️

SQL Projects 👆

Boost your SQL skills with these exciting challenges:

1️⃣ List salesmen and customers from the same city.
2️⃣ Fetch order details for amounts between $500 and $2000.
3️⃣ Identify salesmen working with specific customers.
4️⃣ Find customers whose salesmen earn over 12% commission.
5️⃣ List customers with salesmen in different cities earning more than 12%.
6️⃣ Retrieve complete order details, including salesman commissions.
7️⃣ Join Salesman, Customer, and Order tables for unique insights.
8️⃣ Identify customers with or without assigned salesmen.
9️⃣ Retrieve low-grade customers working independently or with salesmen.
🔟 Report customers placing multiple orders.

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

Like this post if you need more 👍❤️

Hope it helps :)

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