How to Become a Data Analyst from Scratch! 🚀

Whether you're starting fresh or upskilling, here's your roadmap:

➜ Master Excel and SQL - solve SQL problems from leetcode & hackerank
➜ Get the hang of either Power BI or Tableau - do some hands-on projects
➜ learn what the heck ATS is and how to get around it
➜ learn to be ready for any interview question
➜ Build projects for a data portfolio
➜ And you don't need to do it all at once!
➜ Fail and learn to pick yourself up whenever required

Whether it's acing interviews or building an impressive portfolio, give yourself the space to learn, fail, and grow. Good things take time ✅

Like if it helps ❤️

I have curated best 80+ top-notch Data Analytics Resources 👇👇
https://topmate.io/analyst/861634

Hope it helps :)

Essential Pandas Functions for Data Analysis

Data Loading:

pd.read_csv() - Load data from a CSV file.

pd.read_excel() - Load data from an Excel file.


Data Inspection:

df.head(n) - View the first n rows.

df.info() - Get a summary of the dataset.

df.describe() - Generate summary statistics.


Data Manipulation:

df.drop(columns=['col1', 'col2']) - Remove specific columns.

df.rename(columns={'old_name': 'new_name'}) - Rename columns.

df['col'] = df['col'].apply(func) - Apply a function to a column.


Filtering and Sorting:

df[df['col'] > value] - Filter rows based on a condition.

df.sort_values(by='col', ascending=True) - Sort rows by a column.


Aggregation:

df.groupby('col').sum() - Group data and compute the sum.

df['col'].value_counts() - Count unique values in a column.


Merging and Joining:

pd.merge(df1, df2, on='key') - Merge two DataFrames.

pd.concat([df1, df2]) - Concatenate

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

Like this post for more resources like this 👍♥️

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

SQL Essential Concepts for Data Analyst Interviews ✅

1. SQL Syntax: Understand the basic structure of SQL queries, which typically include SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY clauses. Know how to write queries to retrieve data from databases.

2. SELECT Statement: Learn how to use the SELECT statement to fetch data from one or more tables. Understand how to specify columns, use aliases, and perform simple arithmetic operations within a query.

3. WHERE Clause: Use the WHERE clause to filter records based on specific conditions. Familiarize yourself with logical operators like =, >, <, >=, <=, <>, AND, OR, and NOT.

4. JOIN Operations: Master the different types of joins—INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN—to combine rows from two or more tables based on related columns.

5. GROUP BY and HAVING Clauses: Use the GROUP BY clause to group rows that have the same values in specified columns and aggregate data with functions like COUNT(), SUM(), AVG(), MAX(), and MIN(). The HAVING clause filters groups based on aggregate conditions.

6. ORDER BY Clause: Sort the result set of a query by one or more columns using the ORDER BY clause. Understand how to sort data in ascending (ASC) or descending (DESC) order.

7. Aggregate Functions: Be familiar with aggregate functions like COUNT(), SUM(), AVG(), MIN(), and MAX() to perform calculations on sets of rows, returning a single value.

8. DISTINCT Keyword: Use the DISTINCT keyword to remove duplicate records from the result set, ensuring that only unique records are returned.

9. LIMIT/OFFSET Clauses: Understand how to limit the number of rows returned by a query using LIMIT (or TOP in some SQL dialects) and how to paginate results with OFFSET.

10. Subqueries: Learn how to write subqueries, or nested queries, which are queries within another SQL query. Subqueries can be used in SELECT, WHERE, FROM, and HAVING clauses to provide more specific filtering or selection.

11. UNION and UNION ALL: Know the difference between UNION and UNION ALL. UNION combines the results of two queries and removes duplicates, while UNION ALL combines all results including duplicates.

12. IN, BETWEEN, and LIKE Operators: Use the IN operator to match any value in a list, the BETWEEN operator to filter within a range, and the LIKE operator for pattern matching with wildcards (%, _).

13. NULL Handling: Understand how to work with NULL values in SQL, including using IS NULL, IS NOT NULL, and handling nulls in calculations and joins.

14. CASE Statements: Use the CASE statement to implement conditional logic within SQL queries, allowing you to create new fields or modify existing ones based on specific conditions.

15. Indexes: Know the basics of indexing, including how indexes can improve query performance by speeding up the retrieval of rows. Understand when to create an index and the trade-offs in terms of storage and write performance.

16. Data Types: Be familiar with common SQL data types, such as VARCHAR, CHAR, INT, FLOAT, DATE, and BOOLEAN, and understand how to choose the appropriate data type for a column.

17. String Functions: Learn key string functions like CONCAT(), SUBSTRING(), REPLACE(), LENGTH(), TRIM(), and UPPER()/LOWER() to manipulate text data within queries.

18. Date and Time Functions: Master date and time functions such as NOW(), CURDATE(), DATEDIFF(), DATEADD(), and EXTRACT() to handle and manipulate date and time data effectively.

19. INSERT, UPDATE, DELETE Statements: Understand how to use INSERT to add new records, UPDATE to modify existing records, and DELETE to remove records from a table. Be aware of the implications of these operations, particularly in maintaining data integrity.

20. Constraints: Know the role of constraints like PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK in maintaining data integrity and ensuring valid data entry in your database.

Here you can find SQL Interview Resources👇
https://news.1rj.ru/str/DataSimplifier

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

This is how data analytics teams work!

Example:
1) Senior Management at Swiggy/Infosys/HDFC/XYZ company needs data-driven insights to solve a critical business challenge.

So, they onboard a data analytics team to provide support.

2) A team from Analytics Team/Consulting Firm/Internal Data Science Division is onboarded.
The team typically consists of a Lead Analyst/Manager and 2-3 Data Analysts/Junior Analysts.

3) This data analytics team (1 manager + 2-3 analysts) is part of a bigger ecosystem that they can rely upon:
- A Senior Data Scientist/Analytics Lead who has industry knowledge and experience solving similar problems.
- Subject Matter Experts (SMEs) from various domains like AI, Machine Learning, or industry-specific fields (e.g., Marketing, Supply Chain, Finance).
- Business Intelligence (BI) Experts and Data Engineers who ensure that the data is well-structured and easy to interpret.
- External Tools & Platforms (e.g., Power BI, Tableau, Google Analytics) that can be leveraged for advanced analytics.
- Data Experts who specialize in various data sources, research, and methods to get the right information.

4) Every member of this ecosystem collaborates to create value for the client:
- The entire team works toward solving the client’s business problem using data-driven insights.
- The Manager & Analysts may not be industry experts but have access to the right tools and people to bring the expertise required.
- If help is needed from a Data Scientist sitting in New York or a Cloud Engineer in Singapore, it’s available—collaboration is key!

End of the day:
1) Data analytics teams aren’t just about crunching numbers—they’re about solving problems using data-driven insights.
2) EVERYONE in this ecosystem plays a vital role and is rewarded well because the value they create helps the business make informed decisions!
3) You should consider working in this field for a few years, at least. It’ll teach you how to break down complex business problems and solve them with data. And trust me, data-driven decision-making is one of the most powerful skills to have today!

I have curated best 80+ top-notch Data Analytics Resources 👇👇
https://news.1rj.ru/str/DataSimplifier

Like this post for more content like this 👍♥️

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

Goldman Sachs senior data analyst interview asked questions

SQL

1 find avg of salaries department wise from table
2 Write a SQL query to see employee name and manager name using a self-join on 'employees' table with columns 'emp_id', 'name', and 'manager_id'.
3 newest joinee for every department (solved using lead lag)

POWER BI

1. What does Filter context in DAX mean?
2. Explain how to implement Row-Level Security (RLS) in Power BI.
3. Describe different types of filters in Power BI.
4. Explain the difference between 'ALL' and 'ALLSELECTED' in DAX.
5. How do you calculate the total sales for a specific product using DAX?

PYTHON

1. Create a dictionary, add elements to it, modify an element, and then print the dictionary in alphabetical order of keys.
2. Find unique values in a list of assorted numbers and print the count of how many times each value is repeated.
3. Find and print duplicate values in a list of assorted numbers, along with the number of times each value is repeated.

I have curated best 80+ top-notch Data Analytics Resources 👇👇
https://news.1rj.ru/str/DataSimplifier

Hope this helps you 😊

Step-by-step guide to become a Data Analyst in 2025—📊

1. Learn the Fundamentals:
Start with Excel, basic statistics, and data visualization concepts.

2. Pick Up Key Tools & Languages:
Master SQL, Python (or R), and data visualization tools like Tableau or Power BI.

3. Get Formal Education or Certification:
A bachelor’s degree in a relevant field (like Computer Science, Math, or Economics) helps, but you can also do online courses or certifications in data analytics.

4. Build Hands-on Experience:
Work on real-world projects—use Kaggle datasets, internships, or freelance gigs to practice data cleaning, analysis, and visualization.

5. Create a Portfolio:
Showcase your projects on GitHub or a personal website. Include dashboards, reports, and code samples.

6. Develop Soft Skills:
Focus on communication, problem-solving, teamwork, and attention to detail—these are just as important as technical skills.

7. Apply for Entry-Level Jobs:
Look for roles like “Junior Data Analyst” or “Business Analyst.” Tailor your resume to highlight your skills and portfolio.

8. Keep Learning:
Stay updated with new tools (like AI-driven analytics), trends, and advanced topics such as machine learning or domain-specific analytics.

React ❤️ for more

35 Important SQL Interview Questions with Detailed Answers:

1. Explain order of execution of SQL.

Order: FROM → JOIN → ON → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT. SQL queries are processed in this logical sequence, not the way they are written.

2. What is difference between WHERE and HAVING?

WHERE filters rows before aggregation, while HAVING filters groups after aggregation.

3. What is the use of GROUP BY?

GROUP BY aggregates data across rows with the same values in specified columns, commonly used with aggregate functions.

4. Explain all types of joins in SQL?

INNER JOIN: Returns matching rows from both tables.
LEFT JOIN: All rows from the left, matched rows from right.
RIGHT JOIN: All rows from the right, matched rows from left.
FULL JOIN: All rows from both, with NULLs where no match.
SELF JOIN: Joins table to itself.
CROSS JOIN: Cartesian product of both tables.

5. What are triggers in SQL?

Triggers are procedural code executed automatically in response to certain events on a table or view (INSERT, UPDATE, DELETE).

6. What is stored procedure in SQL?

A stored procedure is a set of SQL statements saved and executed on demand, useful for modularizing code.

7. Explain all types of window functions?

RANK(): Gives rank with gaps.
DENSE_RANK(): Ranks without gaps.
ROW_NUMBER(): Unique row index.
LEAD(): Access next row.
LAG(): Access previous row.

8. What is difference between DELETE and TRUNCATE?

DELETE: Row-wise deletion, can have WHERE clause, logs each row.
TRUNCATE: Deletes all rows, faster, minimal logging, cannot rollback easily.

9. What is difference between DML, DDL and DCL?

DML: Data Manipulation Language (SELECT, INSERT, UPDATE, DELETE).
DDL: Data Definition Language (CREATE, ALTER, DROP).
DCL: Data Control Language (GRANT, REVOKE).

10. What are aggregate functions?

Functions that return a single value: SUM(), AVG(), COUNT(), MIN(), MAX().

11. Which is faster: CTE or Subquery?

Performance depends on context, but subqueries are sometimes faster as CTEs may be materialized.

12. What are constraints and types?

Rules to maintain data integrity. Types: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT.

13. Types of Keys?

Primary Key
Foreign Key
Unique Key
Composite Key
Candidate Key

14. Different types of Operators?

Arithmetic: +, -, *, /
Comparison: =, <>, >, <, >=, <=
Logical: AND, OR, NOT
Bitwise, LIKE, IN, BETWEEN

15. Difference between GROUP BY and WHERE?

WHERE filters before aggregation. GROUP BY groups after filtering.

16. What are Views?

Virtual tables based on SQL queries. They store only query definition.

17. What are different types of constraints?

Same as Q12: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT.

18. What is difference between VARCHAR and NVARCHAR?

VARCHAR: ASCII, 1 byte per char.
NVARCHAR: Unicode, 2 bytes per char, supports multiple languages.

19. Similarity for CHAR and NCHAR?

CHAR: Fixed-length ASCII.
NCHAR: Fixed-length Unicode.

20. What are indexes and their types?

Used for faster retrieval.

Types:
- Clustered
- Non-clustered
- Unique
- Composite
- Full-text

21. What is an index? Explain its types.

Same as above. Indexes speed up queries by creating pointers to data.

22. List different types of relationships in SQL.

One-to-One
One-to-Many
Many-to-Many

23. Differentiate between UNION and UNION ALL.

UNION: Removes duplicates.
UNION ALL: Includes duplicates.

24. How many types of clauses in SQL?

Common clauses: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, OFFSET, JOIN, ON.

25. What is the difference between UNION and UNION ALL in SQL?

Same as Q23.

26. What are various types of relationships in SQL?
Same as Q22.

27. Difference between Primary Key and Secondary Key?

Primary Key: Uniquely identifies rows.
Secondary Key: May not be unique, used for lookup.

Credits: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v/1000

Scenario based  Interview Questions & Answers for Data Analyst

1. Scenario: You are working on a SQL database that stores customer information. The database has a table called "Orders" that contains order details. Your task is to write a SQL query to retrieve the total number of orders placed by each customer.
  Question:
  - Write a SQL query to find the total number of orders placed by each customer.
Expected Answer:
    SELECT CustomerID, COUNT(*) AS TotalOrders
    FROM Orders
    GROUP BY CustomerID;

2. Scenario: You are working on a SQL database that stores employee information. The database has a table called "Employees" that contains employee details. Your task is to write a SQL query to retrieve the names of all employees who have been with the company for more than 5 years.
  Question:
  - Write a SQL query to find the names of employees who have been with the company for more than 5 years.
Expected Answer:
    SELECT Name
    FROM Employees
    WHERE DATEDIFF(year, HireDate, GETDATE()) > 5;

Power BI Scenario-Based Questions

1. Scenario: You have been given a dataset in Power BI that contains sales data for a company. Your task is to create a report that shows the total sales by product category and region.
    Expected Answer:
    - Load the dataset into Power BI.
    - Create relationships if necessary.
    - Use the "Fields" pane to select the necessary fields (Product Category, Region, Sales).
    - Drag these fields into the "Values" area of a new visualization (e.g., a table or bar chart).
    - Use the "Filters" pane to filter data as needed.
    - Format the visualization to enhance clarity and readability.

2. Scenario: You have been asked to create a Power BI dashboard that displays real-time stock prices for a set of companies. The stock prices are available through an API.
  Expected Answer:
    - Use Power BI Desktop to connect to the API.
    - Go to "Get Data" > "Web" and enter the API URL.
    - Configure the data refresh settings to ensure real-time updates (e.g., setting up a scheduled refresh or using DirectQuery if supported).
    - Create visualizations using the imported data.
    - Publish the report to the Power BI service and set up a data gateway if needed for continuous refresh.

3. Scenario: You have been given a Power BI report that contains multiple visualizations. The report is taking a long time to load and is impacting the performance of the application.
    Expected Answer:
    - Analyze the current performance using Performance Analyzer.
    - Optimize data model by reducing the number of columns and rows, and removing unnecessary calculations.
    - Use aggregated tables to pre-compute results.
    - Simplify DAX calculations.
    - Optimize visualizations by reducing the number of visuals per page and avoiding complex custom visuals.
    - Ensure proper indexing on the data source.

Free SQL Resources: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Like if you need more similar content

Hope it helps :)

Data Analyst Interview Questions 👇

1.How to create filters in Power BI?

Filters are an integral part of Power BI reports. They are used to slice and dice the data as per the dimensions we want. Filters are created in a couple of ways.

Using Slicers: A slicer is a visual under Visualization Pane. This can be added to the design view to filter our reports. When a slicer is added to the design view, it requires a field to be added to it. For example- Slicer can be added for Country fields. Then the data can be filtered based on countries.
Using Filter Pane: The Power BI team has added a filter pane to the reports, which is a single space where we can add different fields as filters. And these fields can be added depending on whether you want to filter only one visual(Visual level filter), or all the visuals in the report page(Page level filters), or applicable to all the pages of the report(report level filters)


2.How to sort data in Power BI?

Sorting is available in multiple formats. In the data view, a common sorting option of alphabetical order is there. Apart from that, we have the option of Sort by column, where one can sort a column based on another column. The sorting option is available in visuals as well. Sort by ascending and descending option by the fields and measure present in the visual is also available.


3.How to convert pdf to excel?

Open the PDF document you want to convert in XLSX format in Acrobat DC.
Go to the right pane and click on the “Export PDF” option.
Choose spreadsheet as the Export format.
Select “Microsoft Excel Workbook.”
Now click “Export.”
Download the converted file or share it.


4. How to enable macros in excel?

Click the file tab and then click “Options.”
A dialog box will appear. In the “Excel Options” dialog box, click on the “Trust Center” and then “Trust Center Settings.”
Go to the “Macro Settings” and select “enable all macros.”
Click OK to apply the macro settings.

📊 Top 10 Data Analytics Concepts Everyone Should Know 🚀

1️⃣ Data Cleaning 🧹
Removing duplicates, fixing missing or inconsistent data.
👉 Tools: Excel, Python (Pandas), SQL

2️⃣ Denoscriptive Statistics 📈
Mean, median, mode, standard deviation—basic measures to summarize data.
👉 Used for understanding data distribution

3️⃣ Data Visualization 📊
Creating charts and dashboards to spot patterns.
👉 Tools: Power BI, Tableau, Matplotlib, Seaborn

4️⃣ Exploratory Data Analysis (EDA) 🔍
Identifying trends, outliers, and correlations through deep data exploration.
👉 Step before modeling

5️⃣ SQL for Data Extraction 🗃️
Querying databases to retrieve specific information.
👉 Focus on SELECT, JOIN, GROUP BY, WHERE

6️⃣ Hypothesis Testing ⚖️
Making decisions using sample data (A/B testing, p-value, confidence intervals).
👉 Useful in product or marketing experiments

7️⃣ Correlation vs Causation 🔗
Just because two things are related doesn’t mean one causes the other!

8️⃣ Data Modeling 🧠
Creating models to predict or explain outcomes.
👉 Linear regression, decision trees, clustering

9️⃣ KPIs & Metrics 🎯
Understanding business performance indicators like ROI, retention rate, churn.

🔟 Storytelling with Data 🗣️

Translating raw numbers into insights stakeholders can act on.
👉 Use clear visuals, simple language, and real-world impact

❤️ React for more

Steps to 𝐆𝐞𝐭 𝐈𝐧𝐭𝐞𝐫𝐯𝐢𝐞𝐰 𝐂𝐚𝐥𝐥𝐬 from LinkedIn:

1. 𝐀𝐩𝐩𝐥𝐲 𝐃𝐚𝐢𝐥𝐲: Submit applications for 30-40 jobs daily to increase visibility.

2. 𝐃𝐢𝐯𝐞𝐫𝐬𝐢𝐟𝐲 𝐀𝐩𝐩𝐥𝐢𝐜𝐚𝐭𝐢𝐨𝐧𝐬: Apply for various job types, not just "easy apply" options.

3. 𝐀𝐩𝐩𝐥𝐲 𝐏𝐫𝐨𝐦𝐩𝐭𝐥𝐲: Turn on job alerts and apply as soon as positions are posted.

4. 𝐒𝐞𝐞𝐤 𝐑𝐞𝐟𝐞𝐫𝐫𝐚𝐥𝐬: For dream companies, quickly request referrals from employees. Connect with several people for better chances.

5. 𝐁𝐞 𝐃𝐢𝐫𝐞𝐜𝐭 𝐟𝐨𝐫 𝐑𝐞𝐟𝐞𝐫𝐫𝐚𝐥s: Don't start with "Hi" or "Hello". Send a cold message (short and crisp) with what you need and the job link. If you get a response, you can share your resume for referral. Follow up after one day if needed.

6. 𝐀𝐩𝐩𝐥𝐲 𝐖𝐢𝐭𝐡𝐢𝐧 𝐄𝐥𝐢𝐠𝐢𝐛𝐢𝐥𝐢𝐭𝐲: Only apply or seek referrals for roles where you meet the qualifications (or close enough).

7. 𝐎𝐩𝐭𝐢𝐦𝐢𝐳𝐞 𝐘𝐨𝐮𝐫 𝐏𝐫𝐨𝐟𝐢𝐥𝐞: Build a network of 500+ connections, update experiences, use a professional photo, and list relevant skills.

8. 𝐂𝐨𝐧𝐧𝐞𝐜𝐭 𝐰𝐢𝐭𝐡 𝐑𝐞𝐜𝐫𝐮𝐢𝐭𝐞𝐫𝐬: After applying, connect with job posters and recruiters, and send your CV with a cold message (short and crisp).

9. 𝐄𝐧𝐡𝐚𝐧𝐜𝐞 𝐕𝐢𝐬𝐢𝐛𝐢𝐥𝐢𝐭𝐲: Keep your profile visible, send connection requests, and share relevant content.

10. 𝐏𝐞𝐫𝐬𝐨𝐧𝐚𝐥𝐢𝐳𝐞 𝐂𝐨𝐧𝐧𝐞𝐜𝐭𝐢𝐨𝐧 𝐑𝐞𝐪𝐮𝐞𝐬𝐭𝐬: Customize requests to explain your interest.

11. 𝐄𝐧𝐠𝐚𝐠𝐞 𝐰𝐢𝐭𝐡 𝐂𝐨𝐧𝐭𝐞𝐧𝐭: Like, comment, and share posts to stay visible and expand your network.

12. 𝐒𝐡𝐨𝐰𝐜𝐚𝐬𝐞 𝐄𝐱𝐩𝐞𝐫𝐭𝐢𝐬𝐞: Publish articles or posts about your field to attract potential employers.

13. 𝐉𝐨𝐢𝐧 𝐆𝐫𝐨𝐮𝐩𝐬: Participate in industry-related LinkedIn groups to engage and expand your network.

14. 𝐔𝐩𝐝𝐚𝐭𝐞 𝐇𝐞𝐚𝐝𝐥𝐢𝐧𝐞 𝐚𝐧𝐝 𝐒𝐮𝐦𝐦𝐚𝐫𝐲: Reflect your current role, skills, and aspirations with relevant keywords.

15. 𝐑𝐞𝐪𝐮𝐞𝐬𝐭 𝐑𝐞𝐜𝐨𝐦𝐦𝐞𝐧𝐝𝐚𝐭𝐢𝐨𝐧𝐬: Get endorsements from colleagues, managers, and clients.

16. 𝐅𝐨𝐥𝐥𝐨𝐰 𝐂𝐨𝐦𝐩𝐚𝐧𝐢𝐞𝐬: Stay updated on job openings and company news by following your target companies.

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