The Untold Truth About Junior Data Analyst Interviews (From Someone Who’s Seen It All)

Guys, let’s cut through the noise. Most companies aren’t testing how many fancy tools you know—they’re testing how you think! Here’s what you really need to focus on:


SQL Interview Round

WHAT YOU THINK THEY WANT:
“Write the most complex SQL queries!”

WHAT THEY ACTUALLY TEST:

Can you clean messy data?

Do you handle NULL values logically?

How do you deal with duplicates?

Can you explain what you did, step-by-step?

Do you verify your results?


REALISTIC QUESTIONS YOU’LL FACE:
1️⃣ Find duplicate orders in a sales table.
2️⃣ Calculate monthly revenue for the past year.
3️⃣ Identify the top 10 customers by revenue.


Excel Interview Round

WHAT YOU THINK THEY WANT:
“Show off crazy Excel skills with macros and VBA.”

WHAT THEY REALLY WANT TO SEE:

Your ability to use VLOOKUP/XLOOKUP.

Comfort with Pivot Tables for summarization.

Your knack for creating basic formulas for data cleaning.

A logical approach to tackling Excel problems.


REALISTIC TASKS:
✅ Merge two datasets using VLOOKUP.
✅ Summarize sales trends in a Pivot Table.
✅ Clean up inconsistent text fields (hello, TRIM function).


Business Case Analysis

WHAT YOU THINK THEY WANT:
“Build a mind-blowing dashboard or deliver complex models.”

WHAT THEY ACTUALLY EVALUATE:

Can you break down the problem into manageable parts?

Do you ask smart, relevant questions?

Is your analysis focused on business outcomes?

How clearly can you present your findings?


What You'll Definitely Face

1. The “Data Mess” Scenario
They’ll hand you a messy dataset with:

Missing data, duplicates, and weird formats.

No clear instructions.


They watch:
👉 How you approach the problem.
👉 If you spot inconsistencies.
👉 The steps you take to clean and structure data.

2. The “Explain Your Analysis” Challenge
They’ll say:
“Walk us through what you did and why.”

They’re looking for:

Clarity in communication.

Your thought process.

The connection between your work and the business context.

How to Stand Out in Interviews

1. Nail the Basics

SQL: Focus on joins, filtering, grouping, and aggregating.

Excel: Get comfortable with lookups, pivots, and cleaning techniques.

Data Cleaning: Practice handling real-world messy datasets.


2. Understand the Business

Research their industry and common metrics (e.g., sales, churn rate).

Know basic KPIs they might ask about.

Prepare thoughtful, strategic questions.


3. Practice Real Scenarios
🔹 Analyze trends: Monthly revenue, churn analysis.
🔹 Segment customers: Who are your top spenders?
🔹 Evaluate campaigns: Which marketing effort drove the best ROI?


Reality Check: What Really Matters

🌟 How you think through a problem.
🌟 How you communicate your insights.
🌟 How you connect your work to business goals.

🚫 What doesn’t matter?

Writing overly complex SQL.

Knowing every Excel formula.

Advanced machine learning knowledge (for most junior roles).


Pro Tip: Stay calm, ask questions, and show you’re eager to solve problems. Your mindset is just as important as your technical skills!

I know it's a very long post but it'll be worth the efforts I took even if it helps a single person. Give it a like if you want me to continue posting such detailed posts.

I have curated best 80+ top-notch Data Analytics Resources 👇👇
https://whatsapp.com/channel/0029VaGgzAk72WTmQFERKh02

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

Hope it helps :)

Day 8: Working with Joins

1. INNER JOIN

Returns rows with matching values in both tables.


Syntax:

SELECT Table1.Column1, Table2.Column2
FROM Table1
INNER JOIN Table2
ON Table1.CommonColumn = Table2.CommonColumn;

Example:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
INNER JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

2. LEFT JOIN (or LEFT OUTER JOIN)

Returns all rows from the left table and matching rows from the right table. Non-matching rows in the right table return NULL.


Example:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

3. RIGHT JOIN (or RIGHT OUTER JOIN)

Returns all rows from the right table and matching rows from the left table. Non-matching rows in the left table return NULL.


Example:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

4. FULL JOIN (or FULL OUTER JOIN)

Returns all rows when there is a match in either table. Rows without matches return NULL.


Example:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
FULL JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

5. SELF JOIN

A SELF JOIN is a table joined with itself, useful for hierarchical or relationship data.


Syntax:

SELECT A.Column1, B.Column2
FROM TableName A
INNER JOIN TableName B
ON A.CommonColumn = B.CommonColumn;

Example:

-- Find employees with the same manager
SELECT A.Name AS Employee, B.Name AS Manager
FROM Employees A
INNER JOIN Employees B
ON A.ManagerID = B.EmployeeID;

6. CROSS JOIN

Combines each row from the first table with all rows from the second table, creating a Cartesian product.


Syntax:

SELECT *
FROM Table1
CROSS JOIN Table2;

Example:

SELECT Employees.Name, Projects.ProjectName
FROM Employees
CROSS JOIN Projects;

Combining Joins with Filters

Use WHERE or ON to refine join results.

Example:

-- Employees without departments
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID
WHERE Departments.DepartmentName IS NULL;

Action Steps

1. Create two related tables (e.g., Employees and Departments).


2. Practice each join type (INNER, LEFT, RIGHT, FULL).


3. Write queries combining joins with filtering conditions.

🔝 SQL 30 Days Challenge

Here you can find SQL Interview Resources👇
https://365datascience.pxf.io/APy44a

Like this post if you want me to continue this SQL series 👍♥️

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

Hope it helps :)

Day 9: Subqueries and Common Table Expressions (CTEs)

1. Subqueries

A subquery is a query nested inside another query. It can be used in SELECT, FROM, or WHERE clauses.

Types of Subqueries:

1. Single-row Subquery: Returns one row.


2. Multi-row Subquery: Returns multiple rows.


3. Correlated Subquery: Depends on the outer query.


Example 1: Single-row Subquery

-- Find employees earning more than the average salary
SELECT Name, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

Example 2: Multi-row Subquery

-- Find employees in specific departments
SELECT Name
FROM Employees
WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE Location = 'NY');

Example 3: Correlated Subquery

-- Find employees with the highest salary in each department
SELECT Name, Salary
FROM Employees E1
WHERE Salary = (
    SELECT MAX(Salary)
    FROM Employees E2
    WHERE E1.DepartmentID = E2.DepartmentID
);

2. Common Table Expressions (CTEs)

CTEs provide a way to define temporary result sets that can be reused in the main query.

Syntax:

WITH CTEName AS (
SELECT Column1, Column2
FROM TableName
WHERE Condition
)
SELECT *
FROM CTEName;

Example 1: Simple CTE

-- Get total salary per department
WITH DepartmentSalary AS (
    SELECT DepartmentID, SUM(Salary) AS TotalSalary
    FROM Employees
    GROUP BY DepartmentID
)
SELECT *
FROM DepartmentSalary;

Example 2: Recursive CTE Used for hierarchical data (e.g., organizational structures).

WITH RecursiveCTE AS (
    -- Anchor member
    SELECT EmployeeID, ManagerID, Name
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    -- Recursive member
    SELECT E.EmployeeID, E.ManagerID, E.Name
    FROM Employees E
    INNER JOIN RecursiveCTE R
    ON E.ManagerID = R.EmployeeID
)
SELECT *
FROM RecursiveCTE;

Action Steps

1. Practice writing subqueries in WHERE, SELECT, and FROM.

2. Use a CTE to simplify complex queries.

3. Create a recursive CTE for hierarchical data if applicable.

🔝 SQL 30 Days Challenge

Here you can find SQL Interview Resources👇
https://365datascience.pxf.io/APy44a

Like this post if you want me to continue this SQL series 👍♥️

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

Hope it helps :)

If you have time to learn...!

You have time to grow...!

Start from Scratch !!!!

You have time to become a Data Analyst...!!


➜ learn Excel
➜ learn SQL
➜ learn either Power BI or Tableau
➜ learn what the heck ATS is and how to get around it
➜ learn to be ready for any interview question
➜ to build projects for a portfolio
➜ to put invest the time for your future
➜ to fail and pick yourself back up


And you don't need to do it all at once!

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

Hope it helps :)

Day 10: Advanced SQL Functions and Window Functions

1. Advanced SQL Functions

These functions enhance data manipulation and analysis.

1. String Functions:

UPPER(), LOWER(): Change case.

CONCAT(): Combine strings.

SUBSTRING(): Extract part of a string.

TRIM(): Remove leading/trailing spaces.


Example:

SELECT CONCAT(FirstName, ' ', LastName) AS FullName
FROM Employees;


2. Date Functions:

NOW(): Current date and time.

DATEADD(): Add intervals to a date.

DATEDIFF(): Difference between dates.


Example:

SELECT DATEDIFF(DAY, HireDate, GETDATE()) AS DaysWorked
FROM Employees;


3. Mathematical Functions:

ROUND(), CEIL(), FLOOR(), ABS(): Perform numerical operations.


Example:

SELECT ROUND(Salary, 2) AS RoundedSalary
FROM Employees;


2. Window Functions

Window functions perform calculations across a set of rows related to the current row, without collapsing rows like aggregate functions.

1. ROW_NUMBER(): Assigns a unique number to each row in a result set.

SELECT Name, Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
FROM Employees;


2. RANK(): Assigns a rank to rows, with gaps for ties.

SELECT Name, Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employees;


3. DENSE_RANK(): Similar to RANK() but without gaps.

SELECT Name, Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank
FROM Employees;


4. NTILE(): Divides rows into a specified number of groups.

SELECT Name, Salary, NTILE(4) OVER (ORDER BY Salary DESC) AS Quartile
FROM Employees;


5. LEAD() and LAG(): Access data from the next or previous row.

SELECT Name, Salary, LEAD(Salary) OVER (ORDER BY Salary) AS NextSalary
FROM Employees;


6. Aggregate with PARTITION BY:

Use PARTITION BY to calculate aggregates within subsets of data.


Example:

SELECT DepartmentID, Name, Salary, SUM(Salary) OVER (PARTITION BY DepartmentID) AS DepartmentTotal
FROM Employees;


Action Steps

1. Practice string, date, and math functions on your dataset.

2. Implement ROW_NUMBER(), RANK(), and PARTITION BY to analyze data.

3. Use LEAD() and LAG() to compare current rows with previous/next rows.

🔝 SQL 30 Days Challenge

Here you can find SQL Interview Resources👇
https://365datascience.pxf.io/APy44a

Like this post if you want me to continue this SQL series 👍♥️

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

Hope it helps :)

Day 11: Indexes and Performance Optimization

1. What are Indexes?

Indexes improve query performance by allowing the database to find rows more quickly. They act as a data structure that provides a faster way to look up data.

2. Types of Indexes

1. Clustered Index:

Stores data physically in order based on indexed column(s).

Only one per table.

Example: Primary key.


Syntax:

CREATE CLUSTERED INDEX idx_name ON TableName(ColumnName);


2. Non-Clustered Index:

Creates a separate structure for the index while data remains unsorted.

Multiple non-clustered indexes can exist on a table.


Syntax:

CREATE NONCLUSTERED INDEX idx_name ON TableName(ColumnName);


3. Unique Index:

Ensures all values in the indexed column(s) are unique.

Automatically created for PRIMARY KEY and UNIQUE constraints.


Syntax:

CREATE UNIQUE INDEX idx_name ON TableName(ColumnName);


4. Composite Index:

Indexes multiple columns together.


Syntax:

CREATE INDEX idx_name ON TableName(Column1, Column2);


3. Best Practices for Indexing

1. Index columns frequently used in WHERE, JOIN, or ORDER BY.


2. Avoid over-indexing (too many indexes can slow down write operations).


3. Use composite indexes for multi-column searches.


4. Regularly update statistics for accurate query plans.


4. Query Performance Optimization

1. EXPLAIN/Execution Plan:

Use it to analyze query performance and identify bottlenecks.


Syntax:

EXPLAIN SELECT * FROM TableName WHERE Column = 'Value';


2. Avoid SELECT :

Only retrieve required columns to minimize data retrieval.


Example:

SELECT Name, Salary FROM Employees WHERE DepartmentID = 1;


3. Use Joins Efficiently:

Prefer INNER JOIN for better performance if applicable.



4. Optimize WHERE Clauses:

Use indexed columns in WHERE.


Example:

SELECT * FROM Employees WHERE EmployeeID = 101;


5. Avoid Functions in WHERE Clauses:

Functions prevent the use of indexes.


Inefficient:

SELECT * FROM Employees WHERE YEAR(HireDate) = 2023;

Efficient:

SELECT * FROM Employees WHERE HireDate >= '2023-01-01' AND HireDate < '2024-01-01';


6. Use LIMIT/OFFSET:

Reduce the result set size for better performance.

Example:

SELECT * FROM Employees LIMIT 10 OFFSET 0;


5. Dropping Unused Indexes

Too many indexes can slow down write operations. Drop unused ones.

Syntax:

DROP INDEX idx_name ON TableName;

Action Steps

1. Create clustered, non-clustered, and composite indexes on a test table.

2. Use EXPLAIN or execution plans to analyze slow queries.

3. Optimize queries based on the best practices above.

🔝 SQL 30 Days Challenge

Here you can find SQL Interview Resources👇
https://365datascience.pxf.io/APy44a

Like this post if you want me to continue this SQL series 👍♥️

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

Hope it helps :)

Day 12: Transactions and Error Handling

1. What are Transactions?

A transaction is a sequence of SQL operations performed as a single logical unit of work. Transactions ensure data consistency and integrity.

2. ACID Properties of Transactions

1. Atomicity: All operations within the transaction succeed or none do.


2. Consistency: The database remains consistent before and after the transaction.


3. Isolation: Transactions do not interfere with each other.


4. Durability: Once committed, the transaction’s changes are permanent.


3. Transaction Control Statements

1. BEGIN TRANSACTION: Starts a transaction.

BEGIN TRANSACTION;


2. COMMIT: Saves all changes made during the transaction.

COMMIT;


3. ROLLBACK: Undoes all changes made during the transaction.

ROLLBACK;


4. SAVEPOINT: Sets a point within a transaction to roll back to.

SAVEPOINT SavePointName;


5. RELEASE SAVEPOINT: Deletes a savepoint.

RELEASE SAVEPOINT SavePointName;

4. Example of a Transaction

BEGIN TRANSACTION;

-- Deduct from sender's account
UPDATE Accounts
SET Balance = Balance - 1000
WHERE AccountID = 1;

-- Add to receiver's account
UPDATE Accounts
SET Balance = Balance + 1000
WHERE AccountID = 2;

-- Check for errors
IF @@ERROR <> 0
BEGIN
    ROLLBACK;
    PRINT 'Transaction Failed';
END
ELSE
BEGIN
    COMMIT;
    PRINT 'Transaction Successful';
END;

5. Error Handling

1. TRY...CATCH: Handle errors and ensure proper cleanup in case of failure.

Syntax:

BEGIN TRY
    -- SQL statements
END TRY
BEGIN CATCH
    -- Error handling code
END CATCH

2. Example with TRY...CATCH:

BEGIN TRY
    BEGIN TRANSACTION;

    -- Insert operation
    INSERT INTO Employees (Name, Salary) VALUES ('John', 5000);

    -- Error-prone operation
    INSERT INTO Employees (Name, Salary) VALUES (NULL, NULL);

    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK;
    PRINT 'Error occurred: ' + ERROR_MESSAGE();
END CATCH;

3. @@ERROR:

A system function that returns the error code of the last T-SQL statement.


6. Isolation Levels

Control how transactions interact with each other.

1. Read Uncommitted: Allows dirty reads.

2. Read Committed: Prevents dirty reads.

3. Repeatable Read: Prevents non-repeatable reads.

4. Serializable: Prevents dirty, non-repeatable, and phantom reads.

Syntax:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
-- SQL operations
COMMIT;

Action Steps

1. Write a transaction with BEGIN TRANSACTION, COMMIT, and ROLLBACK.


2. Implement error handling using TRY...CATCH.


3. Experiment with different isolation levels in test scenarios.

🔝 SQL 30 Days Challenge

Here you can find SQL Interview Resources👇
https://365datascience.pxf.io/APy44a

Like this post if you want me to continue this SQL series 👍♥️

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

Hope it helps :)

Top Tableau Features Every Data Analyst Should Know

Data Connection:

Connect to Multiple Data Sources: Blend data from files, databases, and cloud platforms.

Live vs. Extract: Choose between real-time data updates or working with a snapshot.


Visualizations:

Drag-and-Drop Interface: Quickly create bar charts, line graphs, and heat maps.

Dual-Axis Charts: Compare two measures with separate axes.

Trend Lines: Add statistical trend lines to visuals.


Filters and Parameters:

Interactive Filters: Allow users to filter data dynamically.

Parameters: Let users input values to customize analysis (e.g., thresholds).


Calculated Fields:

Custom Calculations: Create metrics like profit ratios or rolling averages.

Logical Functions: Use IF, CASE, and other functions for custom logic.


Dashboards:

Combine Views: Merge multiple sheets into a single dashboard.

Actions: Add interactivity like filters or URL actions.


Geospatial Analysis:

Map Visualizations: Plot data points on a map using lat-long or names.

Filled Maps: Visualize regions (e.g., countries, states) with color gradients.


Sharing and Publishing:

Tableau Public: Publish visuals for public access.

Tableau Server/Online: Share dashboards securely within an organization.

Best Resources to learn Tableau: https://whatsapp.com/channel/0029VasYW1V5kg6z4EHOHG1t

Like this post if you want me to continue this Tableau series 👍♥️

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

Hope it helps :)

Day 13: Views, Stored Procedures, and Triggers

1. Views

A view is a virtual table based on a SQL query. It simplifies complex queries and improves data abstraction.

1. Creating a View:

CREATE VIEW ViewName AS
SELECT Column1, Column2
FROM TableName
WHERE Condition;

2. Using a View:

SELECT * FROM ViewName;


3. Updating a View:

Views can often be updated if based on a single table and meet certain criteria.


Example:

UPDATE ViewName
SET Column1 = 'NewValue'
WHERE Condition;


4. Dropping a View:

DROP VIEW ViewName;


2. Stored Procedures

A stored procedure is a set of SQL statements stored in the database and executed as a single unit.

1. Creating a Stored Procedure:

CREATE PROCEDURE ProcedureName
AS
BEGIN
    SELECT * FROM TableName WHERE Condition;
END;

2. Executing a Stored Procedure:

EXEC ProcedureName;


3. Stored Procedure with Parameters:

CREATE PROCEDURE GetEmployeeDetails @EmployeeID INT
AS
BEGIN
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;

Execution:

EXEC GetEmployeeDetails @EmployeeID = 1;

4. Dropping a Stored Procedure:

DROP PROCEDURE ProcedureName;


3. Triggers

Triggers are SQL code automatically executed in response to specific events on a table.

1. Types of Triggers:

AFTER Trigger: Executes after an INSERT, UPDATE, or DELETE operation.

INSTEAD OF Trigger: Replaces the triggering action.



2. Creating an AFTER Trigger:

CREATE TRIGGER TriggerName
ON TableName
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    PRINT 'Trigger executed';
END;

3. Example: Logging Changes:

CREATE TRIGGER LogChanges
ON Employees
AFTER UPDATE
AS
BEGIN
    INSERT INTO AuditLog (EmployeeID, ChangeTime)
    SELECT EmployeeID, GETDATE()
    FROM Inserted;
END;

4. Dropping a Trigger:

DROP TRIGGER TriggerName;

4. Use Cases

1. Views: Simplify reporting or provide restricted access to data.


2. Stored Procedures: Automate repetitive tasks or enforce business logic.


3. Triggers: Automatically maintain audit trails or enforce rules.

Action Steps

1. Create a view to simplify a complex query.

2. Write a stored procedure to retrieve specific data based on a parameter.

3. Create a trigger to log changes in a table.

🔝 SQL 30 Days Challenge

Here you can find SQL Interview Resources👇
https://365datascience.pxf.io/APy44a

Like this post if you want me to continue this SQL series 👍♥️

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

Hope it helps :)

Cloud-Based Data Analysis Tools

Google BigQuery:

Purpose: Query and analyze large datasets.

Strengths: Scalable, serverless, integrates with Google Cloud.


Amazon Redshift:

Purpose: Data warehousing and analytics.

Strengths: Handles massive datasets with fast query speeds.


Microsoft Azure Synapse Analytics:

Purpose: Integrates big data and data warehousing.

Strengths: Seamless with Power BI and other Azure services.


Snowflake:

Purpose: Cloud data platform for storage and computation.

Strengths: Elastic scalability, easy-to-use SQL interface.


Databricks:

Purpose: Unified analytics for big data and machine learning.

Strengths: Ideal for collaboration and advanced ML workloads.


Tableau Online:

Purpose: Cloud-hosted analytics for sharing visualizations.

Strengths: Real-time dashboards and collaboration.

I have curated best 80+ top-notch Data Analytics Resources 👇👇
t.me/datasimplifier

Like this post for more content like this 👍♥️

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

Hope it helps :)

Day 14: Common Table Expressions (CTEs) and Recursive Queries

1. Common Table Expressions (CTEs)

A Common Table Expression (CTE) is a temporary result set that simplifies complex queries. It exists only during the execution of the query.


2. Syntax of a CTE

WITH CTE_Name (Column1, Column2, ...)
AS
(
    SELECT Column1, Column2
    FROM TableName
    WHERE Condition
)
SELECT * FROM CTE_Name;

3. Example of a CTE

Simple CTE:

WITH EmployeeCTE AS
(
    SELECT EmployeeID, Name, Salary
    FROM Employees
    WHERE Salary > 5000
)
SELECT * FROM EmployeeCTE;

4. Recursive CTE

A recursive CTE refers to itself and is commonly used to query hierarchical data like organizational charts or folder structures.

Syntax:

WITH RecursiveCTE (Column1, Column2, ...)
AS
(
    -- Anchor member
    SELECT Column1, Column2
    FROM TableName
    WHERE Condition

    UNION ALL

    -- Recursive member
    SELECT Column1, Column2
    FROM TableName
    INNER JOIN RecursiveCTE
    ON TableName.ParentID = RecursiveCTE.ID
)
SELECT * FROM RecursiveCTE;

5. Example of a Recursive CTE

Hierarchy of Employees:

WITH EmployeeHierarchy AS
(
    -- Anchor member
    SELECT EmployeeID, ManagerID, Name
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    -- Recursive member
    SELECT e.EmployeeID, e.ManagerID, e.Name
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh
    ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;

6. Key Points to Remember

1. Use CTEs to break down complex queries for better readability.


2. Recursive CTEs must include:

An anchor member (base case).

A recursive member with a termination condition (e.g., ManagerID IS NULL).



3. Recursive queries must include a UNION ALL operator.


7. Benefits of CTEs

1. Improved query readability.


2. Simplifies hierarchical or recursive queries.


3. Can be referenced multiple times within the same query.


Action Steps

1. Write a simple CTE to filter data from a table.


2. Create a recursive CTE to display a hierarchical structure like an organization chart.


3. Test your recursive CTE with a termination condition to avoid infinite loops.

🔝 SQL 30 Days Challenge

Here you can find SQL Interview Resources👇
https://365datascience.pxf.io/APy44a

Like this post if you want me to continue this SQL series 👍♥️

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

Hope it helps :)

Many people pay too much to learn Python, but my mission is to break down barriers. I have shared complete learning series to learn Python from scratch.

Here are the links to the Python series

Complete Python Topics for Data Analyst: https://news.1rj.ru/str/sqlspecialist/548

Part-1: https://news.1rj.ru/str/sqlspecialist/562

Part-2: https://news.1rj.ru/str/sqlspecialist/564

Part-3: https://news.1rj.ru/str/sqlspecialist/565

Part-4: https://news.1rj.ru/str/sqlspecialist/566

Part-5: https://news.1rj.ru/str/sqlspecialist/568

Part-6: https://news.1rj.ru/str/sqlspecialist/570

Part-7: https://news.1rj.ru/str/sqlspecialist/571

Part-8: https://news.1rj.ru/str/sqlspecialist/572

Part-9: https://news.1rj.ru/str/sqlspecialist/578

Part-10: https://news.1rj.ru/str/sqlspecialist/577

Part-11: https://news.1rj.ru/str/sqlspecialist/578

Part-12:
https://news.1rj.ru/str/sqlspecialist/581

Part-13: https://news.1rj.ru/str/sqlspecialist/583

Part-14: https://news.1rj.ru/str/sqlspecialist/584

Part-15: https://news.1rj.ru/str/sqlspecialist/585

I saw a lot of big influencers copy pasting my content after removing the credits. It's absolutely fine for me as more people are getting free education because of my content.

But I will really appreciate if you share credits for the time and efforts I put in to create such valuable content. I hope you can understand.

Complete SQL Topics for Data Analysts: https://news.1rj.ru/str/sqlspecialist/523

Complete Power BI Topics for Data Analysts: https://news.1rj.ru/str/sqlspecialist/588

Thanks to all who support our channel and share the content with proper credits. You guys are really amazing.

Hope it helps :)

Day 15: Window Functions

1. What are Window Functions?

Window functions perform calculations across a set of rows related to the current row, helping analyze data without grouping.

2. Types of Window Functions

Aggregate Functions: SUM(), AVG(), COUNT(), MIN(), MAX().

Ranking Functions: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE().

Value Functions: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE().

3. Syntax

FunctionName() OVER ( PARTITION BY ColumnName ORDER BY ColumnName )

4. Examples

a) Aggregate with PARTITION

Calculate total salary for each department:

SELECT EmployeeID, DepartmentID, Salary, SUM(Salary) OVER (PARTITION BY DepartmentID) AS TotalSalary FROM Employees;

In Department 101, if employees earn 5000, 6000, and 4000, the total salary for all rows is 15000.

In Department 102, with only one employee earning 7000, the total salary is 7000.

b) ROW_NUMBER

Assign a unique number to each row based on salary:

SELECT EmployeeID, Name, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNumber FROM Employees;

If salaries are 7000, 6000, 5000, employees are ranked as 1, 2, and 3 respectively.

c) RANK and DENSE_RANK
Rank employees based on salary:

SELECT EmployeeID, Name, Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank, DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank FROM Employees;

With salaries 7000, 6000, 6000:

RANK: 1, 2, 2 (skips 3 for ties).

DENSE_RANK: 1, 2, 2 (does not skip numbers for ties).

d) LAG and LEAD

Fetch the previous and next salaries in a sequence:

SELECT EmployeeID, Name, Salary, LAG(Salary) OVER (ORDER BY Salary) AS PreviousSalary, LEAD(Salary) OVER (ORDER BY Salary) AS NextSalary FROM Employees;

For salaries 4000, 5000, 6000:

PreviousSalary: NULL, 4000, 5000.

NextSalary: 5000, 6000, NULL.

5. Key Takeaways

PARTITION BY groups data into subsets for calculations.
ORDER BY defines the sequence for calculations.
Use window functions to analyze data efficiently without grouping rows.

Action Steps

- Write a query using SUM() and PARTITION BY to calculate group totals.

- Use ROW_NUMBER to rank rows based on any column.

- Experiment with LAG and LEAD to fetch previous and next row values.

🔝 SQL 30 Days Challenge

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

Like this post if you want me to continue this SQL series 👍♥️

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

Hope it helps :)

𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿: You have only 2 minutes to solve this Power BI task.

Retrieve the department name and the highest salary in each department from the 'Employees' table, but only for departments where the highest salary is greater than $70,000.

𝗠𝗲: Challenge accepted!

1️⃣ Add a New Measure: To calculate the highest salary per department, use:

Highest_Salary = CALCULATE(MAX(Employees[Salary]), ALLEXCEPT(Employees, Employees[Department]))

2️⃣ Create a Filtered Table: Next, create a table visual to show only departments with a salary over $70,000. Apply a filter to display departments where:

Highest_Salary > 70000

This solution demonstrates my ability to use DAX measures and filters effectively to meet specific business needs in Power BI.

𝗧𝗶𝗽 𝗳𝗼𝗿 𝗣𝗼𝘄𝗲𝗿 𝗕𝗜 𝗝𝗼𝗯 𝗦𝗲𝗲𝗸𝗲𝗿𝘀: Focus on mastering DAX, relationships, and visual-level filters to make your reports more insightful and responsive. It’s about building impactful, user-friendly dashboards, not just complex models!

Interview Resources👇
https://whatsapp.com/channel/0029VaGgzAk72WTmQFERKh02

Like this post if you need more 👍❤️

Hope it helps! :)

Day 16: Views and Materialized Views

1. What are Views?

A view is a virtual table based on a SELECT query. It does not store data itself but retrieves data from underlying tables when queried.

2. Why Use Views?

1. Simplify complex queries.

2. Enhance security by exposing only specific columns.

3. Maintain consistency across multiple queries.


4. Make queries reusable.


3. Syntax for Creating a View

CREATE VIEW ViewName AS
SELECT Column1, Column2
FROM TableName
WHERE Condition;

Example:
Create a view to show employees with a salary greater than 5000:

CREATE VIEW HighSalaryEmployees AS
SELECT EmployeeID, Name, Salary
FROM Employees
WHERE Salary > 5000;

To query the view:

SELECT * FROM HighSalaryEmployees;

4. Updating Data via Views

If the view is based on a single table and includes all the necessary primary keys, you can update data through the view:

UPDATE HighSalaryEmployees
SET Salary = 7000
WHERE EmployeeID = 1;

5. Dropping a View

To remove a view:

DROP VIEW ViewName;

6. Materialized Views

A materialized view stores query results physically, unlike regular views. It is refreshed periodically to reflect changes in the underlying data.

7. Why Use Materialized Views?

1. Improve query performance for complex calculations.

2. Reduce load on the database for frequently used queries.

8. Syntax for Creating a Materialized View

CREATE MATERIALIZED VIEW MaterializedViewName
AS
SELECT Column1, Column2
FROM TableName
WHERE Condition;

9. Refreshing Materialized Views

Materialized views can be refreshed manually or automatically:

-- Manually refresh
REFRESH MATERIALIZED VIEW MaterializedViewName;

10. Key Differences Between Views and Materialized Views

Views: Always fetch data in real-time from the underlying tables.

Materialized Views: Store data physically and need refreshing to update.

Action Steps

1. Create a view for frequently used queries in your database.

2. Try updating data through a view (if allowed).

3. Experiment with creating and refreshing materialized views.

🔝 SQL 30 Days Challenge

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

Like this post if you want me to continue this SQL series 👍♥️

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

Hope it helps :)

5 Essential Portfolio Projects for data analysts 😄👇

1. Exploratory Data Analysis (EDA) on a Real Dataset: Choose a dataset related to your interests, perform thorough EDA, visualize trends, and draw insights. This showcases your ability to understand data and derive meaningful conclusions.

Free websites to find datasets: https://news.1rj.ru/str/DataPortfolio/8

2. Predictive Modeling Project: Build a predictive model, such as a linear regression or classification model. Use a dataset to train and test your model, and evaluate its performance. Highlight your skills in machine learning and statistical analysis.

3. Data Cleaning and Transformation: Take a messy dataset and demonstrate your skills in cleaning and transforming data. Showcase your ability to handle missing values, outliers, and prepare data for analysis.

4. Dashboard Creation: Utilize tools like Tableau or Power BI to create an interactive dashboard. This project demonstrates your ability to present data insights in a visually appealing and user-friendly manner.

5. Time Series Analysis: Work with time-series data to forecast future trends. This could involve stock prices, weather data, or any other time-dependent dataset. Showcase your understanding of time-series concepts and forecasting techniques.

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

Like it if you need more posts like this 😄❤️

Hope it helps :)

Day 17: Indexes

1. What are Indexes?

Indexes are database objects that speed up the retrieval of data from tables. They function like a book’s index, allowing quick access to specific rows.

2. Types of Indexes

Clustered Index:

Alters the physical order of table data.
Each table can have only one clustered index.
Example: Index on a primary key.

Non-Clustered Index:

Does not change the physical order of data but creates a separate structure for quick lookups.
A table can have multiple non-clustered indexes.

Unique Index:

Ensures that values in a column or group of columns are unique.

Composite Index:

An index on multiple columns for queries involving those columns.

3. Why Use Indexes?

Improve query performance for large datasets.

Speed up searches, joins, and filtering.

Enforce uniqueness with unique indexes.

4. Syntax for Creating Indexes
a) Clustered Index

Automatically created when a primary key is defined:

CREATE CLUSTERED INDEX IndexName ON TableName (ColumnName);

Example:

CREATE CLUSTERED INDEX IDX_EmployeeID ON Employees (EmployeeID);

b) Non-Clustered Index

CREATE NONCLUSTERED INDEX IndexName ON TableName (ColumnName);

Example:

CREATE NONCLUSTERED INDEX IDX_Salary ON Employees (Salary);

c) Unique Index

CREATE UNIQUE INDEX IndexName ON TableName (ColumnName);

Example:

CREATE UNIQUE INDEX IDX_UniqueEmail ON Employees (Email);

5. Viewing Indexes

To list all indexes on a table:
EXEC sp_helpindex 'TableName';

6. Dropping Indexes

To remove an index:
DROP INDEX IndexName ON TableName;

Example:

DROP INDEX IDX_Salary ON Employees;

7. Limitations of Indexes

Slows down INSERT, UPDATE, and DELETE operations due to maintenance.

Requires additional storage.
Too many indexes can degrade performance.

8. Best Practices:

Use indexes for frequently queried columns.

Avoid indexing small tables or columns with low selectivity.

Regularly monitor and optimize index usage.

Action Steps:

Create clustered and non-clustered indexes for common queries in your database.
Check the performance difference using indexed vs non-indexed columns.
Drop unused or redundant indexes.

🔝 SQL 30 Days Challenge

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

Like this post if you want me to continue this SQL series 👍♥️

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

Hope it helps :)

If you're new to data analytics, start with SQL and Excel. Focus on one at a time, master it, and then move on to the next. Don’t juggle multiple things at once; finish what you start before taking up something new.

SQL Topics:
SELECT statements
WHERE clause
GROUP BY and HAVING
JOINS (INNER, LEFT, RIGHT, FULL)
Aggregation functions (SUM, COUNT, AVG, etc.)
Common Table Expressions (CTEs)
Subqueries

Excel Topics:
Basic formulas (SUM, IF, VLOOKUP, etc.)
Data cleaning techniques
Pivot tables
Conditional formatting
Charts and graphs
Data validation
Advanced features like Power Query and Macros

SQL Learning Series: https://news.1rj.ru/str/sqlspecialist/567

Excel Learning Series: https://news.1rj.ru/str/sqlspecialist/664

Hope it helps :)

Day 18: Transactions and ACID Properties

1. What are Transactions?

A transaction is a sequence of operations performed as a single unit of work. It ensures data consistency, even in cases of failure.

2. Key Characteristics of Transactions:

Atomicity: Ensures all operations within the transaction are completed or none at all.
Consistency: Guarantees the database remains in a valid state before and after the transaction.
Isolation: Transactions are independent and do not interfere with each other.
Durability: Once a transaction is committed, the changes are permanent.

3. Syntax for Transactions:

Start a Transaction
BEGIN TRANSACTION;

Commit a Transaction
Saves the changes made during the transaction:
COMMIT;

Rollback a Transaction
Reverts the changes made during the transaction:
ROLLBACK;

4. Example

Without Transactions

If an error occurs, only part of the data may be saved, causing inconsistencies:

UPDATE Accounts SET Balance = Balance - 500 WHERE AccountID = 1; UPDATE Accounts SET Balance = Balance + 500 WHERE AccountID = 2;

With Transactions

Ensures both operations are completed or none:

BEGIN TRANSACTION; UPDATE Accounts SET Balance = Balance - 500 WHERE AccountID = 1; UPDATE Accounts SET Balance = Balance + 500 WHERE AccountID = 2; IF @@ERROR <> 0 ROLLBACK; ELSE COMMIT;

If either UPDATE fails, the entire transaction is rolled back.

5. Savepoints

Savepoints allow partial rollback within a transaction:

BEGIN TRANSACTION; UPDATE Accounts SET Balance = Balance - 500 WHERE AccountID = 1; SAVE TRANSACTION SavePoint1; UPDATE Accounts SET Balance = Balance + 500 WHERE AccountID = 2; ROLLBACK TRANSACTION SavePoint1; -- Reverts the second update only COMMIT;

6. Isolation Levels:

Control how transactions interact with each other:

Read Uncommitted: Allows dirty reads (data not yet committed).

Read Committed: Prevents dirty reads (default in most databases).

Repeatable Read: Prevents dirty reads and ensures no changes to data during the transaction.

Serializable: Ensures complete isolation but may reduce performance.
Set the isolation level:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN TRANSACTION;

7. Best Practices:

Keep transactions short to reduce locking and improve performance.

Always use COMMIT or ROLLBACK explicitly.

Test for errors within transactions to handle rollbacks.

Use appropriate isolation levels based on requirements.

Action Steps:

Write a transaction to transfer money between two accounts.

Experiment with savepoints for partial rollbacks.

Explore the effect of different isolation levels on concurrent transactions.

🔝 SQL 30 Days Challenge

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

Like this post if you want me to continue this SQL series 👍♥️

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

Hope it helps :)

As a data analyst, your primary goal isn’t just to create dashboards, write SQL queries, build pivot tables, generate reports, or clean data. While these are important technical tasks, they are merely tools in your toolkit.

Your real focus should be on solving business problems and providing actionable insights. This means understanding the context of the problem, identifying the right data to analyze, and interpreting the results in a way that adds value to the business. Use these technical skills strategically to answer key questions, identify opportunities, and help drive informed decision-making.

Always remember, the ultimate purpose of your work is to contribute to business growth and efficiency by solving problems, not just completing tasks.

Here you can find entire data analytics roadmap with free resources: https://news.1rj.ru/str/free4unow_backup/902

You can join this channel to find latest data analytics job opportunities: https://news.1rj.ru/str/jobs_SQL

Hope it helps :)

Day 19: Stored Procedures

1. What is a Stored Procedure?

A stored procedure is a precompiled set of SQL statements that can be executed with a single call. It helps improve performance, maintainability, and security.

2. Why Use Stored Procedures?

✅ Reduce redundant code.
✅ Improve query performance.
✅ Enhance security by controlling access to direct queries.
✅ Allow parameterized queries for dynamic execution.

3. Syntax for Creating a Stored Procedure

CREATE PROCEDURE ProcedureName AS BEGIN -- SQL statements END;

Example:

A procedure to fetch all employees:

CREATE PROCEDURE GetAllEmployees AS BEGIN SELECT * FROM Employees; END; 
Execute the procedure:
EXEC GetAllEmployees;

4. Stored Procedures with Parameters

Stored procedures can take input and output parameters.

Example:

Procedure to fetch employees based on department ID:

CREATE PROCEDURE GetEmployeesByDept @DeptID INT AS BEGIN SELECT * FROM Employees WHERE DepartmentID = @DeptID; END; 
Execute with a parameter:
EXEC GetEmployeesByDept @DeptID = 2;

5. Stored Procedure with Output Parameters

Used to return values from a procedure.

Example:

A procedure to count employees in a department:

CREATE PROCEDURE GetEmployeeCountByDept @DeptID INT, @EmpCount INT OUTPUT AS BEGIN SELECT @EmpCount = COUNT(*) FROM Employees WHERE DepartmentID = @DeptID; END; 

Call the procedure and get the output value:

DECLARE @Count INT; EXEC GetEmployeeCountByDept @DeptID = 2, @EmpCount = @Count OUTPUT; PRINT @Count; 

6. Modifying and Dropping a Stored Procedure

Modify an existing procedure:

ALTER PROCEDURE ProcedureName AS BEGIN 
-- Updated SQL statements END; 
Drop a stored procedure:
DROP PROCEDURE ProcedureName; 

7. Best Practices for Stored Procedures

Use meaningful names for easy identification.

Avoid SELECT ; instead, specify required columns.

Use parameters instead of hardcoded values.

Handle errors using TRY...CATCH.

Action Steps:

Create a stored procedure to insert a new employee into the Employees table.

Write a procedure with an input parameter for filtering records.

Experiment with an output parameter to return calculated values.

🔝 SQL 30 Days Challenge

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

Like this post if you want me to continue this SQL series 👍♥️

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

Hope it helps :)