Essential Skills Excel for Data Analysts 🚀

1️⃣ Data Cleaning & Transformation

Remove Duplicates – Ensure unique records.
Find & Replace – Quick data modifications.
Text Functions – TRIM, LEN, LEFT, RIGHT, MID, PROPER.
Data Validation – Restrict input values.

2️⃣ Data Analysis & Manipulation

Sorting & Filtering – Organize and extract key insights.
Conditional Formatting – Highlight trends, outliers.
Pivot Tables – Summarize large datasets efficiently.
Power Query – Automate data transformation.

3️⃣ Essential Formulas & Functions

Lookup Functions – VLOOKUP, HLOOKUP, XLOOKUP, INDEX-MATCH.
Logical Functions – IF, AND, OR, IFERROR, IFS.
Aggregation Functions – SUM, AVERAGE, MIN, MAX, COUNT, COUNTA.
Text Functions – CONCATENATE, TEXTJOIN, SUBSTITUTE.

4️⃣ Data Visualization
Charts & Graphs – Bar, Line, Pie, Scatter, Histogram.

Sparklines – Miniature charts inside cells.
Conditional Formatting – Color scales, data bars.
Dashboard Creation – Interactive and dynamic reports.

5️⃣ Advanced Excel Techniques
Array Formulas – Dynamic calculations with multiple values.
Power Pivot & DAX – Advanced data modeling.
What-If Analysis – Goal Seek, Scenario Manager.
Macros & VBA – Automate repetitive tasks.

6️⃣ Data Import & Export
CSV & TXT Files – Import and clean raw data.
Power Query – Connect to databases, web sources.
Exporting Reports – PDF, CSV, Excel formats.

Here you can find some free Excel books & useful resources: https://t.me/excel_data

Hope it helps :)

#dataanalyst

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

🔥 Recent Data Analyst Interview Q&A at Deloitte 🔥

Question:
👉 Write an SQL query to extract the third highest salary from an employee table with columns EID and ESalary.

Solution:

SELECT ESalary  
FROM (  
  SELECT ESalary,  
         DENSE_RANK() OVER (ORDER BY ESalary DESC) AS salary_rank  
  FROM employee  
) AS ranked_salaries  
WHERE salary_rank = 3;

Explanation of the Query:

1️⃣ Step 1: Create a Subquery

The subquery ranks all salaries in descending order using DENSE_RANK().

2️⃣ Step 2: Rank the Salaries

Assigns ranks: 1 for the highest salary, 2 for the second-highest, and so on.

3️⃣ Step 3: Assign an Alias

The subquery is given an alias (ranked_salaries) to use in the main query.

4️⃣ Step 4: Filter for the Third Highest Salary

The WHERE clause filters the results to include only the salary with rank 3.

5️⃣ Step 5: Display the Third Highest Salary

The main query selects and displays the third-highest salary.

By following these steps, you can easily extract the third-highest salary from the table.



#DataAnalyst #SQL #InterviewTips