๐๐๐ ๐๐๐ฌ๐ ๐๐ญ๐ฎ๐๐ข๐๐ฌ ๐๐จ๐ซ ๐๐ง๐ญ๐๐ซ๐ฏ๐ข๐๐ฐ:
Join for more: https://t.iss.one/sqlanalyst
1. Dannyโs Diner:
Restaurant analytics to understand the customer orders pattern.
Link: https://8weeksqlchallenge.com/case-study-1/
2. Pizza Runner
Pizza shop analytics to optimize the efficiency of the operation
Link: https://8weeksqlchallenge.com/case-study-2/
3. Foodie Fie
Subscription-based food content platform
Link: https://lnkd.in/gzB39qAT
4. Data Bank: Thatโs money
Analytics based on customer activities with the digital bank
Link: https://lnkd.in/gH8pKPyv
5. Data Mart: Fresh is Best
Analytics on Online supermarket
Link: https://lnkd.in/gC5bkcDf
6. Clique Bait: Attention capturing
Analytics on the seafood industry
Link: https://lnkd.in/ggP4JiYG
7. Balanced Tree: Clothing Company
Analytics on the sales performance of clothing store
Link: https://8weeksqlchallenge.com/case-study-7
8. Fresh segments: Extract maximum value
Analytics on online advertising
Link: https://8weeksqlchallenge.com/case-study-8
Join for more: https://t.iss.one/sqlanalyst
1. Dannyโs Diner:
Restaurant analytics to understand the customer orders pattern.
Link: https://8weeksqlchallenge.com/case-study-1/
2. Pizza Runner
Pizza shop analytics to optimize the efficiency of the operation
Link: https://8weeksqlchallenge.com/case-study-2/
3. Foodie Fie
Subscription-based food content platform
Link: https://lnkd.in/gzB39qAT
4. Data Bank: Thatโs money
Analytics based on customer activities with the digital bank
Link: https://lnkd.in/gH8pKPyv
5. Data Mart: Fresh is Best
Analytics on Online supermarket
Link: https://lnkd.in/gC5bkcDf
6. Clique Bait: Attention capturing
Analytics on the seafood industry
Link: https://lnkd.in/ggP4JiYG
7. Balanced Tree: Clothing Company
Analytics on the sales performance of clothing store
Link: https://8weeksqlchallenge.com/case-study-7
8. Fresh segments: Extract maximum value
Analytics on online advertising
Link: https://8weeksqlchallenge.com/case-study-8
๐5
Advanced Questions Asked by Big 4
๐ Excel Questions
1. How do you use Excel to forecast future trends based on historical data? Describe a scenario where you built a forecasting model.
2. Can you explain how you would automate repetitive tasks in Excel using VBA (Visual Basic for Applications)? Provide an example of a complex macro you created.
3. Describe a time when you had to merge and analyze data from multiple Excel workbooks. How did you ensure data integrity and accuracy?
๐ SQL Questions
1. How would you design a database schema for a new e-commerce platform to efficiently handle large volumes of transactions and user data?
2. Describe a complex SQL query you wrote to solve a business problem. What was the problem, and how did your query help resolve it?
3. How do you ensure data integrity and consistency in a multi-user database environment? Explain the techniques and tools you use.
๐ Python Questions
1. How would you use Python to automate data extraction from various APIs and combine the data for analysis? Provide an example.
2. Describe a machine learning project you worked on using Python. What was the objective, and how did you approach the data preprocessing, model selection, and evaluation?
3. Explain how you would use Python to detect and handle anomalies in a dataset. What techniques and libraries would you employ?
๐ Power BI Questions
1. How do you create interactive dashboards in Power BI that can dynamically update based on user inputs? Provide an example of a dashboard you built.
2. Describe a scenario where you used Power BI to integrate data from non-traditional sources (e.g., web scraping, APIs). How did you handle the data transformation and visualization?
3. How do you ensure the performance and scalability of Power BI reports when dealing with large datasets? Describe the techniques and best practices you follow.
๐ก Tips for Success:
Understand the business context: Tailor your answers to show how your technical skills solve real business problems.
Provide specific examples: Highlight your past experiences with concrete examples.
Stay updated: Continuously learn and adapt to new tools and methodologies.
I have curated best 80+ top-notch Data Analytics Resources ๐๐
https://whatsapp.com/channel/0029VaGgzAk72WTmQFERKh02
Hope it helps :)
๐ Excel Questions
1. How do you use Excel to forecast future trends based on historical data? Describe a scenario where you built a forecasting model.
2. Can you explain how you would automate repetitive tasks in Excel using VBA (Visual Basic for Applications)? Provide an example of a complex macro you created.
3. Describe a time when you had to merge and analyze data from multiple Excel workbooks. How did you ensure data integrity and accuracy?
๐ SQL Questions
1. How would you design a database schema for a new e-commerce platform to efficiently handle large volumes of transactions and user data?
2. Describe a complex SQL query you wrote to solve a business problem. What was the problem, and how did your query help resolve it?
3. How do you ensure data integrity and consistency in a multi-user database environment? Explain the techniques and tools you use.
๐ Python Questions
1. How would you use Python to automate data extraction from various APIs and combine the data for analysis? Provide an example.
2. Describe a machine learning project you worked on using Python. What was the objective, and how did you approach the data preprocessing, model selection, and evaluation?
3. Explain how you would use Python to detect and handle anomalies in a dataset. What techniques and libraries would you employ?
๐ Power BI Questions
1. How do you create interactive dashboards in Power BI that can dynamically update based on user inputs? Provide an example of a dashboard you built.
2. Describe a scenario where you used Power BI to integrate data from non-traditional sources (e.g., web scraping, APIs). How did you handle the data transformation and visualization?
3. How do you ensure the performance and scalability of Power BI reports when dealing with large datasets? Describe the techniques and best practices you follow.
๐ก Tips for Success:
Understand the business context: Tailor your answers to show how your technical skills solve real business problems.
Provide specific examples: Highlight your past experiences with concrete examples.
Stay updated: Continuously learn and adapt to new tools and methodologies.
I have curated best 80+ top-notch Data Analytics Resources ๐๐
https://whatsapp.com/channel/0029VaGgzAk72WTmQFERKh02
Hope it helps :)
๐6
SQL Joins โ Essential Concepts ๐
1๏ธโฃ What Are SQL Joins?
SQL Joins are used to combine rows from two or more tables based on a related column.
2๏ธโฃ Types of Joins
INNER JOIN: Returns only matching rows from both tables.
SELECT * FROM TableA INNER JOIN TableB ON TableA.id = TableB.id;
LEFT JOIN (LEFT OUTER JOIN): Returns all rows from the left table and matching rows from the right table.
SELECT * FROM TableA LEFT JOIN TableB ON TableA.id = TableB.id;
RIGHT JOIN (RIGHT OUTER JOIN): Returns all rows from the right table and matching rows from the left table.
SELECT * FROM TableA RIGHT JOIN TableB ON TableA.id = TableB.id;
FULL JOIN (FULL OUTER JOIN): Returns all rows when there is a match in either table.
SELECT * FROM TableA FULL JOIN TableB ON TableA.id = TableB.id;
3๏ธโฃ Self Join
A table joins with itself to compare rows.
SELECT A.name, B.name FROM Employees A JOIN Employees B ON A.manager_id = B.id;
4๏ธโฃ Cross Join
Returns the Cartesian product of both tables (every row from Table A pairs with every row from Table B).
SELECT * FROM TableA CROSS JOIN TableB;
5๏ธโฃ Joins with Multiple Conditions
Using multiple columns for matching.
SELECT * FROM TableA INNER JOIN TableB ON TableA.id = TableB.id AND TableA.type = TableB.type;
6๏ธโฃ Using Aliases in Joins
Shortens table names for better readability.
SELECT A.name, B.salary FROM Employees A INNER JOIN Salaries B ON A.id = B.emp_id;
7๏ธโฃ Handling NULLs in Joins
Use COALESCE(column, default_value) to replace NULL values.
IS NULL to filter unmatched rows in LEFT or RIGHT JOINs.
Free SQL Resources: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v
React with โค๏ธ for free resources
Share with credits: https://t.iss.one/sqlspecialist
Hope it helps :)
1๏ธโฃ What Are SQL Joins?
SQL Joins are used to combine rows from two or more tables based on a related column.
2๏ธโฃ Types of Joins
INNER JOIN: Returns only matching rows from both tables.
SELECT * FROM TableA INNER JOIN TableB ON TableA.id = TableB.id;
LEFT JOIN (LEFT OUTER JOIN): Returns all rows from the left table and matching rows from the right table.
SELECT * FROM TableA LEFT JOIN TableB ON TableA.id = TableB.id;
RIGHT JOIN (RIGHT OUTER JOIN): Returns all rows from the right table and matching rows from the left table.
SELECT * FROM TableA RIGHT JOIN TableB ON TableA.id = TableB.id;
FULL JOIN (FULL OUTER JOIN): Returns all rows when there is a match in either table.
SELECT * FROM TableA FULL JOIN TableB ON TableA.id = TableB.id;
3๏ธโฃ Self Join
A table joins with itself to compare rows.
SELECT A.name, B.name FROM Employees A JOIN Employees B ON A.manager_id = B.id;
4๏ธโฃ Cross Join
Returns the Cartesian product of both tables (every row from Table A pairs with every row from Table B).
SELECT * FROM TableA CROSS JOIN TableB;
5๏ธโฃ Joins with Multiple Conditions
Using multiple columns for matching.
SELECT * FROM TableA INNER JOIN TableB ON TableA.id = TableB.id AND TableA.type = TableB.type;
6๏ธโฃ Using Aliases in Joins
Shortens table names for better readability.
SELECT A.name, B.salary FROM Employees A INNER JOIN Salaries B ON A.id = B.emp_id;
7๏ธโฃ Handling NULLs in Joins
Use COALESCE(column, default_value) to replace NULL values.
IS NULL to filter unmatched rows in LEFT or RIGHT JOINs.
Free SQL Resources: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v
React with โค๏ธ for free resources
Share with credits: https://t.iss.one/sqlspecialist
Hope it helps :)
๐6๐1
Complete SQL Topics for Data Analysts ๐๐
1. Introduction to SQL:
- Basic syntax and structure
- Understanding databases and tables
2. Querying Data:
- SELECT statement
- Filtering data using WHERE clause
- Sorting data with ORDER BY
3. Joins:
- INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN
- Combining data from multiple tables
4. Aggregation Functions:
- GROUP BY
- Aggregate functions like COUNT, SUM, AVG, MAX, MIN
5. Subqueries:
- Using subqueries in SELECT, WHERE, and HAVING clauses
6. Data Modification:
- INSERT, UPDATE, DELETE statements
- Transactions and Rollback
7. Data Types and Constraints:
- Understanding various data types (e.g., INT, VARCHAR)
- Using constraints (e.g., PRIMARY KEY, FOREIGN KEY)
8. Indexes:
- Creating and managing indexes for performance optimization
9. Views:
- Creating and using views for simplified querying
10. Stored Procedures and Functions:
- Writing and executing stored procedures
- Creating and using functions
11. Normalization:
- Understanding database normalization concepts
12. Data Import and Export:
- Importing and exporting data using SQL
13. Window Functions:
- ROW_NUMBER(), RANK(), DENSE_RANK(), and others
14. Advanced Filtering:
- Using CASE statements for conditional logic
15. Advanced Join Techniques:
- Self-joins and other advanced join scenarios
16. Analytical Functions:
- LAG(), LEAD(), OVER() for advanced analytics
17. Working with Dates and Times:
- Date and time functions and formatting
18. Performance Tuning:
- Query optimization strategies
19. Security:
- Understanding SQL injection and best practices for security
20. Handling NULL Values:
- Dealing with NULL values in queries
Ensure hands-on practice on these topics to strengthen your SQL skills.
Since SQL is one of the most essential skill for data analysts, I have decided to teach each topic daily in this channel for free. Like this post if you want me to continue this SQL series ๐โฅ๏ธ
Share with credits: https://t.iss.one/sqlspecialist
Hope it helps :)
1. Introduction to SQL:
- Basic syntax and structure
- Understanding databases and tables
2. Querying Data:
- SELECT statement
- Filtering data using WHERE clause
- Sorting data with ORDER BY
3. Joins:
- INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN
- Combining data from multiple tables
4. Aggregation Functions:
- GROUP BY
- Aggregate functions like COUNT, SUM, AVG, MAX, MIN
5. Subqueries:
- Using subqueries in SELECT, WHERE, and HAVING clauses
6. Data Modification:
- INSERT, UPDATE, DELETE statements
- Transactions and Rollback
7. Data Types and Constraints:
- Understanding various data types (e.g., INT, VARCHAR)
- Using constraints (e.g., PRIMARY KEY, FOREIGN KEY)
8. Indexes:
- Creating and managing indexes for performance optimization
9. Views:
- Creating and using views for simplified querying
10. Stored Procedures and Functions:
- Writing and executing stored procedures
- Creating and using functions
11. Normalization:
- Understanding database normalization concepts
12. Data Import and Export:
- Importing and exporting data using SQL
13. Window Functions:
- ROW_NUMBER(), RANK(), DENSE_RANK(), and others
14. Advanced Filtering:
- Using CASE statements for conditional logic
15. Advanced Join Techniques:
- Self-joins and other advanced join scenarios
16. Analytical Functions:
- LAG(), LEAD(), OVER() for advanced analytics
17. Working with Dates and Times:
- Date and time functions and formatting
18. Performance Tuning:
- Query optimization strategies
19. Security:
- Understanding SQL injection and best practices for security
20. Handling NULL Values:
- Dealing with NULL values in queries
Ensure hands-on practice on these topics to strengthen your SQL skills.
Since SQL is one of the most essential skill for data analysts, I have decided to teach each topic daily in this channel for free. Like this post if you want me to continue this SQL series ๐โฅ๏ธ
Share with credits: https://t.iss.one/sqlspecialist
Hope it helps :)
๐10โค1
๐ SQL Cheatsheet โ Quick Reference Guide
Whether youโre just starting out with databases or you need a handy reminder while coding, keep this sheet in your pocket and query with confidence!
---
Whether youโre just starting out with databases or you need a handy reminder while coding, keep this sheet in your pocket and query with confidence!
---
-- 1๏ธโฃ Database Basics
CREATE DATABASE db_name;
USE db_name;
-- 2๏ธโฃ Tables
CREATE TABLE table_name (col1 datatype, col2 datatype);
DROP TABLE table_name;
ALTER TABLE table_name ADD column_name datatype;
-- 3๏ธโฃ Insert Data
INSERT INTO table_name (col1, col2) VALUES (val1, val2);
-- 4๏ธโฃ Select Queries
SELECT * FROM table_name;
SELECT col1, col2 FROM table_name;
SELECT * FROM table_name WHERE condition;
-- 5๏ธโฃ Update Data
UPDATE table_name SET col1 = value1 WHERE condition;
-- 6๏ธโฃ Delete Data
DELETE FROM table_name WHERE condition;
-- 7๏ธโฃ Joins
SELECT * FROM table1
INNER JOIN table2 ON table1.col = table2.col;
SELECT * FROM table1
LEFT JOIN table2 ON table1.col = table2.col;
SELECT * FROM table1
RIGHT JOIN table2 ON table1.col = table2.col;
-- 8๏ธโฃ Aggregations
SELECT COUNT(*) FROM table_name;
SELECT SUM(col) FROM table_name;
SELECT col, COUNT(*) FROM table_name GROUP BY col;
-- 9๏ธโฃ Sorting & Limiting
SELECT * FROM table_name ORDER BY col ASC; -- or DESC
SELECT * FROM table_name LIMIT n;
-- ๐ Indexes
CREATE INDEX idx_name ON table_name (col);
DROP INDEX idx_name;
-- 1๏ธโฃ1๏ธโฃ Subqueries
SELECT * FROM table_name
WHERE col IN (SELECT col FROM other_table);
-- 1๏ธโฃ2๏ธโฃ Views
CREATE VIEW view_name AS
SELECT * FROM table_name;
DROP VIEW view_name;
๐17โค3๐3
SQL Interview Questions for 0-1 year of Experience (Asked in Top Product-Based Companies).
Sharpen your SQL skills with these real interview questions!
Q1. Customer Purchase Patterns -
You have two tables, Customers and Purchases: CREATE TABLE Customers ( customer_id INT PRIMARY KEY, customer_name VARCHAR(255) ); CREATE TABLE Purchases ( purchase_id INT PRIMARY KEY, customer_id INT, product_id INT, purchase_date DATE );
Assume necessary INSERT statements are already executed.
Write an SQL query to find the names of customers who have purchased more than 5 different products within the last month. Order the result by customer_name.
Q2. Call Log Analysis -
Suppose you have a CallLogs table: CREATE TABLE CallLogs ( log_id INT PRIMARY KEY, caller_id INT, receiver_id INT, call_start_time TIMESTAMP, call_end_time TIMESTAMP );
Assume necessary INSERT statements are already executed.
Write a query to find the average call duration per user. Include only users who have made more than 10 calls in total. Order the result by average duration descending.
Q3. Employee Project Allocation - Consider two tables, Employees and Projects:
CREATE TABLE Employees ( employee_id INT PRIMARY KEY, employee_name VARCHAR(255), department VARCHAR(255) ); CREATE TABLE Projects ( project_id INT PRIMARY KEY, lead_employee_id INT, project_name VARCHAR(255), start_date DATE, end_date DATE );
Assume necessary INSERT statements are already executed.
The goal is to write an SQL query to find the names of employees who have led more than 3 projects in the last year. The result should be ordered by the number of projects led.
Sharpen your SQL skills with these real interview questions!
Q1. Customer Purchase Patterns -
You have two tables, Customers and Purchases: CREATE TABLE Customers ( customer_id INT PRIMARY KEY, customer_name VARCHAR(255) ); CREATE TABLE Purchases ( purchase_id INT PRIMARY KEY, customer_id INT, product_id INT, purchase_date DATE );
Assume necessary INSERT statements are already executed.
Write an SQL query to find the names of customers who have purchased more than 5 different products within the last month. Order the result by customer_name.
Q2. Call Log Analysis -
Suppose you have a CallLogs table: CREATE TABLE CallLogs ( log_id INT PRIMARY KEY, caller_id INT, receiver_id INT, call_start_time TIMESTAMP, call_end_time TIMESTAMP );
Assume necessary INSERT statements are already executed.
Write a query to find the average call duration per user. Include only users who have made more than 10 calls in total. Order the result by average duration descending.
Q3. Employee Project Allocation - Consider two tables, Employees and Projects:
CREATE TABLE Employees ( employee_id INT PRIMARY KEY, employee_name VARCHAR(255), department VARCHAR(255) ); CREATE TABLE Projects ( project_id INT PRIMARY KEY, lead_employee_id INT, project_name VARCHAR(255), start_date DATE, end_date DATE );
Assume necessary INSERT statements are already executed.
The goal is to write an SQL query to find the names of employees who have led more than 3 projects in the last year. The result should be ordered by the number of projects led.
๐3โค1
1. What is the difference between the RANK() and DENSE_RANK() functions?
The RANK() function in the result set defines the rank of each row within your ordered partition. If both rows have the same rank, the next number in the ranking will be the previous rank plus a number of duplicates. If we have three records at rank 4, for example, the next level indicated is 7. The DENSE_RANK() function assigns a distinct rank to each row within a partition based on the provided column value, with no gaps. If we have three records at rank 4, for example, the next level indicated is 5.
2. Explain One-hot encoding and Label Encoding. How do they affect the dimensionality of the given dataset?
One-hot encoding is the representation of categorical variables as binary vectors. Label Encoding is converting labels/words into numeric form. Using one-hot encoding increases the dimensionality of the data set. Label encoding doesnโt affect the dimensionality of the data set. One-hot encoding creates a new variable for each level in the variable whereas, in Label encoding, the levels of a variable get encoded as 1 and 0.
3. Explain the Difference Between Tableau Worksheet, Dashboard, Story, and Workbook in Tableau?
Tableau uses a workbook and sheet file structure, much like Microsoft Excel.
A workbook contains sheets, which can be a worksheet, dashboard, or a story.
A worksheet contains a single view along with shelves, legends, and the Data pane.
A dashboard is a collection of views from multiple worksheets.
A story contains a sequence of worksheets or dashboards that work together to convey information.
4. How can you split a column into 2 or more columns?
You can split a column into 2 or more columns by following the below steps:
1. Select the cell that you want to split. Then, navigate to the Data tab, after that, select Text to Columns. 2. Select the delimiter. 3. Choose the column data format and select the destination you want to display the split. 4. The final output will look like below where the text is split into multiple columns.
5. Do you wanna make your career in Data Science & Analytics but don't know how to start ?
https://t.iss.one/sqlspecialist/851
Here are free resources that will make you technically strong enough to crack any Data Analyst and also learn Pro Career Growth Hacks to land on your Dream Job.
The RANK() function in the result set defines the rank of each row within your ordered partition. If both rows have the same rank, the next number in the ranking will be the previous rank plus a number of duplicates. If we have three records at rank 4, for example, the next level indicated is 7. The DENSE_RANK() function assigns a distinct rank to each row within a partition based on the provided column value, with no gaps. If we have three records at rank 4, for example, the next level indicated is 5.
2. Explain One-hot encoding and Label Encoding. How do they affect the dimensionality of the given dataset?
One-hot encoding is the representation of categorical variables as binary vectors. Label Encoding is converting labels/words into numeric form. Using one-hot encoding increases the dimensionality of the data set. Label encoding doesnโt affect the dimensionality of the data set. One-hot encoding creates a new variable for each level in the variable whereas, in Label encoding, the levels of a variable get encoded as 1 and 0.
3. Explain the Difference Between Tableau Worksheet, Dashboard, Story, and Workbook in Tableau?
Tableau uses a workbook and sheet file structure, much like Microsoft Excel.
A workbook contains sheets, which can be a worksheet, dashboard, or a story.
A worksheet contains a single view along with shelves, legends, and the Data pane.
A dashboard is a collection of views from multiple worksheets.
A story contains a sequence of worksheets or dashboards that work together to convey information.
4. How can you split a column into 2 or more columns?
You can split a column into 2 or more columns by following the below steps:
1. Select the cell that you want to split. Then, navigate to the Data tab, after that, select Text to Columns. 2. Select the delimiter. 3. Choose the column data format and select the destination you want to display the split. 4. The final output will look like below where the text is split into multiple columns.
5. Do you wanna make your career in Data Science & Analytics but don't know how to start ?
https://t.iss.one/sqlspecialist/851
Here are free resources that will make you technically strong enough to crack any Data Analyst and also learn Pro Career Growth Hacks to land on your Dream Job.
๐4๐2โค1
Interviewer: You mentioned that you had reduced cloud storage costs by 50%.
Candidate: Yeah!
Interviewer: How?
Candidate: ๐๐๐๐๐๐ * ๐๐ซ๐จ๐ฆ ๐๐๐๐๐๐๐๐๐ ๐ฐ๐ก๐๐ซ๐ ๐ข๐%๐==๐
Candidate: Yeah!
Interviewer: How?
Candidate: ๐๐๐๐๐๐ * ๐๐ซ๐จ๐ฆ ๐๐๐๐๐๐๐๐๐ ๐ฐ๐ก๐๐ซ๐ ๐ข๐%๐==๐
๐คฃ9๐3
Here are some essential SQL tips for beginners ๐๐
โ Primary Key = Unique Key + Not Null constraint
โ To perform case insensitive search use UPPER() function ex. UPPER(customer_name) LIKE โA%Aโ
โ LIKE operator is for string data type
โ COUNT(*), COUNT(1), COUNT(0) all are same
โ All aggregate functions ignore the NULL values
โ Aggregate functions MIN, MAX, SUM, AVG, COUNT are for int data type whereas STRING_AGG is for string data type
โ For row level filtration use WHERE and aggregate level filtration use HAVING
โ UNION ALL will include duplicates where as UNION excludes duplicates
โ If the results will not have any duplicates, use UNION ALL instead of UNION
โ We have to alias the subquery if we are using the columns in the outer select query
โ Subqueries can be used as output with NOT IN condition.
โ CTEs look better than subqueries. Performance wise both are same.
โ When joining two tables , if one table has only one value then we can use 1=1 as a condition to join the tables. This will be considered as CROSS JOIN.
โ Window functions work at ROW level.
โ The difference between RANK() and DENSE_RANK() is that RANK() skips the rank if the values are the same.
โ EXISTS works on true/false conditions. If the query returns at least one value, the condition is TRUE. All the records corresponding to the conditions are returned.
Like for more ๐๐
โ Primary Key = Unique Key + Not Null constraint
โ To perform case insensitive search use UPPER() function ex. UPPER(customer_name) LIKE โA%Aโ
โ LIKE operator is for string data type
โ COUNT(*), COUNT(1), COUNT(0) all are same
โ All aggregate functions ignore the NULL values
โ Aggregate functions MIN, MAX, SUM, AVG, COUNT are for int data type whereas STRING_AGG is for string data type
โ For row level filtration use WHERE and aggregate level filtration use HAVING
โ UNION ALL will include duplicates where as UNION excludes duplicates
โ If the results will not have any duplicates, use UNION ALL instead of UNION
โ We have to alias the subquery if we are using the columns in the outer select query
โ Subqueries can be used as output with NOT IN condition.
โ CTEs look better than subqueries. Performance wise both are same.
โ When joining two tables , if one table has only one value then we can use 1=1 as a condition to join the tables. This will be considered as CROSS JOIN.
โ Window functions work at ROW level.
โ The difference between RANK() and DENSE_RANK() is that RANK() skips the rank if the values are the same.
โ EXISTS works on true/false conditions. If the query returns at least one value, the condition is TRUE. All the records corresponding to the conditions are returned.
Like for more ๐๐
โค2๐2
Key SQL Concepts for Data Analyst Interviews
1. Joins: Understand how to use INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN to combine data from different tables, ensuring you can retrieve the needed information from relational databases.
2. Group By and Aggregate Functions: Master GROUP BY along with aggregate functions like COUNT(), SUM(), AVG(), MAX(), and MIN() to summarize data and generate meaningful reports.
3. Data Filtering: Use WHERE, HAVING, and CASE statements to filter and manipulate data effectively, enabling precise data extraction based on specific conditions.
4. Subqueries: Employ subqueries to retrieve data nested within other queries, allowing for more complex data retrieval and analysis scenarios.
5. Window Functions: Leverage window functions such as ROW_NUMBER(), RANK(), DENSE_RANK(), and LAG() to perform calculations across a set of table rows, returning result sets with contextual calculations.
6. Data Types: Ensure proficiency in choosing and handling various SQL data types (VARCHAR, INT, DATE, etc.) to store and query data accurately.
7. Indexes: Learn how to create and manage indexes to speed up the retrieval of data from databases, particularly in tables with large volumes of records.
8. Normalization: Apply normalization principles to organize database tables efficiently, reducing redundancy and improving data integrity.
9. CTEs and Views: Utilize Common Table Expressions (CTEs) and Views to write modular, reusable, and readable queries, making complex data analysis tasks more manageable.
10. Data Import/Export: Know how to import and export data between SQL databases and other tools like BI tools to facilitate comprehensive data analysis workflows.
Here you can find SQL Interview Resources๐
https://t.iss.one/DataSimplifier
Share with credits: https://t.iss.one/sqlspecialist
Hope it helps :)
1. Joins: Understand how to use INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN to combine data from different tables, ensuring you can retrieve the needed information from relational databases.
2. Group By and Aggregate Functions: Master GROUP BY along with aggregate functions like COUNT(), SUM(), AVG(), MAX(), and MIN() to summarize data and generate meaningful reports.
3. Data Filtering: Use WHERE, HAVING, and CASE statements to filter and manipulate data effectively, enabling precise data extraction based on specific conditions.
4. Subqueries: Employ subqueries to retrieve data nested within other queries, allowing for more complex data retrieval and analysis scenarios.
5. Window Functions: Leverage window functions such as ROW_NUMBER(), RANK(), DENSE_RANK(), and LAG() to perform calculations across a set of table rows, returning result sets with contextual calculations.
6. Data Types: Ensure proficiency in choosing and handling various SQL data types (VARCHAR, INT, DATE, etc.) to store and query data accurately.
7. Indexes: Learn how to create and manage indexes to speed up the retrieval of data from databases, particularly in tables with large volumes of records.
8. Normalization: Apply normalization principles to organize database tables efficiently, reducing redundancy and improving data integrity.
9. CTEs and Views: Utilize Common Table Expressions (CTEs) and Views to write modular, reusable, and readable queries, making complex data analysis tasks more manageable.
10. Data Import/Export: Know how to import and export data between SQL databases and other tools like BI tools to facilitate comprehensive data analysis workflows.
Here you can find SQL Interview Resources๐
https://t.iss.one/DataSimplifier
Share with credits: https://t.iss.one/sqlspecialist
Hope it helps :)
๐3๐1
If you have ever given an SQL interview some of the questions would be definitely from below list :
1- How to find duplicates in a table
2- How to delete duplicates from a table
3- Difference between union and union all
4- Difference between rank,row_number and dense_rank
5- Find records in a table which are not present in another table
6- Find second highest salary employees in each department
7- Find employees with salary more than their manager's salary
8- Difference between inner and left join
9- update a table and swap gender values.
If not exact at least flavor of these questions are always asked in interviews irrespective of your experience level
1- How to find duplicates in a table
2- How to delete duplicates from a table
3- Difference between union and union all
4- Difference between rank,row_number and dense_rank
5- Find records in a table which are not present in another table
6- Find second highest salary employees in each department
7- Find employees with salary more than their manager's salary
8- Difference between inner and left join
9- update a table and swap gender values.
If not exact at least flavor of these questions are always asked in interviews irrespective of your experience level
๐4๐2
Do not wait till you've mastered SQL till you apply to your first Data Analyst Job.
You can do both at the same time.
You can do both at the same time.
โค2
10 SQL Concepts Every Data Analyst Should Master ๐
โ SELECT, WHERE, ORDER BY โ Core of querying your data
โ JOINs (INNER, LEFT, RIGHT, FULL) โ Combine data from multiple tables
โ GROUP BY & HAVING โ Aggregate and filter grouped data
โ Subqueries โ Nest queries inside queries for complex logic
โ CTEs (Common Table Expressions) โ Write cleaner, reusable SQL logic
โ Window Functions โ Perform advanced analytics like rankings & running totals
โ Indexes โ Boost your query performance
โ Normalization โ Structure your database efficiently
โ UNION vs UNION ALL โ Combine result sets with or without duplicates
โ Stored Procedures & Functions โ Reusable logic inside your DB
React with โค๏ธ if you want me to cover each topic in detail
Share with credits: https://t.iss.one/sqlspecialist
Hope it helps :)
โ SELECT, WHERE, ORDER BY โ Core of querying your data
โ JOINs (INNER, LEFT, RIGHT, FULL) โ Combine data from multiple tables
โ GROUP BY & HAVING โ Aggregate and filter grouped data
โ Subqueries โ Nest queries inside queries for complex logic
โ CTEs (Common Table Expressions) โ Write cleaner, reusable SQL logic
โ Window Functions โ Perform advanced analytics like rankings & running totals
โ Indexes โ Boost your query performance
โ Normalization โ Structure your database efficiently
โ UNION vs UNION ALL โ Combine result sets with or without duplicates
โ Stored Procedures & Functions โ Reusable logic inside your DB
React with โค๏ธ if you want me to cover each topic in detail
Share with credits: https://t.iss.one/sqlspecialist
Hope it helps :)
๐4๐1
Hey guys,
Today, letโs talk about SQL conceptual questions that are often asked in data analyst interviews. These questions test not only your technical skills but also your conceptual understanding of SQL and its real-world applications.
1. What is the difference between SQL and NoSQL?
- SQL (Structured Query Language) is a relational database management system, meaning it uses tables (rows and columns) to store data.
- NoSQL databases, on the other hand, handle unstructured data and donโt rely on a schema, making them more flexible in terms of data storage and retrieval.
- Interview Tip: Don't just memorize definitions. Be prepared to explain scenarios where youโd use SQL over NoSQL, and vice versa.
2. What is the difference between INNER JOIN and OUTER JOIN?
- An INNER JOIN returns records that have matching values in both tables.
- An OUTER JOIN returns all records from one table and the matched records from the second table. If there's no match, NULL values are returned.
3. How do you optimize a SQL query for better performance?
- Indexing: Create indexes on columns used frequently in WHERE, JOIN, or GROUP BY clauses.
- Query optimization: Use appropriate WHERE clauses to reduce the data set and avoid unnecessary calculations.
- Avoid SELECT *: Always specify the columns you need to reduce the amount of data retrieved.
- Limit results: If you only need a subset of the data, use the LIMIT clause.
4. What are the different types of SQL constraints?
Constraints are used to enforce rules on data in a table. They ensure the accuracy and reliability of the data. The most common types are:
- PRIMARY KEY: Ensures each record is unique and not null.
- FOREIGN KEY: Enforces a relationship between two tables.
- UNIQUE: Ensures all values in a column are unique.
- NOT NULL: Prevents NULL values from being entered into a column.
- CHECK: Ensures a column's values meet a specific condition.
5. What is normalization? What are the different normal forms?
Normalization is the process of organizing data to reduce redundancy and improve data integrity. Hereโs a quick overview of normal forms:
- 1NF (First Normal Form): Ensures that all values in a table are atomic (indivisible).
- 2NF (Second Normal Form): Ensures that the table is in 1NF and that all non-key columns are fully dependent on the primary key.
- 3NF (Third Normal Form): Ensures that the table is in 2NF and all columns are independent of each other except for the primary key.
6. What is a subquery?
A subquery is a query within another query. It's used to perform operations that need intermediate results before generating the final query.
Example:
In this case, the subquery calculates the average salary, and the outer query selects employees whose salary is greater than the average.
7. What is the difference between a UNION and a UNION ALL?
- UNION combines the result sets of two SELECT statements and removes duplicates.
- UNION ALL combines the result sets and includes duplicates.
8. What is the difference between WHERE and HAVING clause?
- WHERE filters rows before any groupings are made. Itโs used with SELECT, INSERT, UPDATE, or DELETE statements.
- HAVING filters groups after the GROUP BY clause.
9. How would you handle NULL values in SQL?
NULL values can represent missing or unknown data. Hereโs how to manage them:
- Use IS NULL or IS NOT NULL in WHERE clauses to filter null values.
- Use COALESCE() or IFNULL() to replace NULL values with default ones.
Example:
10. What is the purpose of the GROUP BY clause?
The GROUP BY clause groups rows with the same values into summary rows. Itโs often used with aggregate functions like COUNT, SUM, AVG, etc.
Example:
Here you can find SQL Interview Resources๐
https://t.iss.one/DataSimplifier
Share with credits: https://t.iss.one/sqlspecialist
Hope it helps :)
Today, letโs talk about SQL conceptual questions that are often asked in data analyst interviews. These questions test not only your technical skills but also your conceptual understanding of SQL and its real-world applications.
1. What is the difference between SQL and NoSQL?
- SQL (Structured Query Language) is a relational database management system, meaning it uses tables (rows and columns) to store data.
- NoSQL databases, on the other hand, handle unstructured data and donโt rely on a schema, making them more flexible in terms of data storage and retrieval.
- Interview Tip: Don't just memorize definitions. Be prepared to explain scenarios where youโd use SQL over NoSQL, and vice versa.
2. What is the difference between INNER JOIN and OUTER JOIN?
- An INNER JOIN returns records that have matching values in both tables.
- An OUTER JOIN returns all records from one table and the matched records from the second table. If there's no match, NULL values are returned.
3. How do you optimize a SQL query for better performance?
- Indexing: Create indexes on columns used frequently in WHERE, JOIN, or GROUP BY clauses.
- Query optimization: Use appropriate WHERE clauses to reduce the data set and avoid unnecessary calculations.
- Avoid SELECT *: Always specify the columns you need to reduce the amount of data retrieved.
- Limit results: If you only need a subset of the data, use the LIMIT clause.
4. What are the different types of SQL constraints?
Constraints are used to enforce rules on data in a table. They ensure the accuracy and reliability of the data. The most common types are:
- PRIMARY KEY: Ensures each record is unique and not null.
- FOREIGN KEY: Enforces a relationship between two tables.
- UNIQUE: Ensures all values in a column are unique.
- NOT NULL: Prevents NULL values from being entered into a column.
- CHECK: Ensures a column's values meet a specific condition.
5. What is normalization? What are the different normal forms?
Normalization is the process of organizing data to reduce redundancy and improve data integrity. Hereโs a quick overview of normal forms:
- 1NF (First Normal Form): Ensures that all values in a table are atomic (indivisible).
- 2NF (Second Normal Form): Ensures that the table is in 1NF and that all non-key columns are fully dependent on the primary key.
- 3NF (Third Normal Form): Ensures that the table is in 2NF and all columns are independent of each other except for the primary key.
6. What is a subquery?
A subquery is a query within another query. It's used to perform operations that need intermediate results before generating the final query.
Example:
SELECT employee_id, name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
In this case, the subquery calculates the average salary, and the outer query selects employees whose salary is greater than the average.
7. What is the difference between a UNION and a UNION ALL?
- UNION combines the result sets of two SELECT statements and removes duplicates.
- UNION ALL combines the result sets and includes duplicates.
8. What is the difference between WHERE and HAVING clause?
- WHERE filters rows before any groupings are made. Itโs used with SELECT, INSERT, UPDATE, or DELETE statements.
- HAVING filters groups after the GROUP BY clause.
9. How would you handle NULL values in SQL?
NULL values can represent missing or unknown data. Hereโs how to manage them:
- Use IS NULL or IS NOT NULL in WHERE clauses to filter null values.
- Use COALESCE() or IFNULL() to replace NULL values with default ones.
Example:
SELECT name, COALESCE(age, 0) AS age
FROM employees;
10. What is the purpose of the GROUP BY clause?
The GROUP BY clause groups rows with the same values into summary rows. Itโs often used with aggregate functions like COUNT, SUM, AVG, etc.
Example:
SELECT department, COUNT(*)
FROM employees
GROUP BY department;
Here you can find SQL Interview Resources๐
https://t.iss.one/DataSimplifier
Share with credits: https://t.iss.one/sqlspecialist
Hope it helps :)
๐9โค1๐1
Majority of top companies hiring for analytic roles (Data Analyst/Business Analyst) focus heavily on SQL understanding as a selection criteria, which according to me, should be the first thing you start your preparation with.
I have divided this SQL roadmap into 3 steps (Basics, Level Up & Practice), and it should take around 1 month to complete.
Step 1 - Basics ๐ข :
โกWhat is a Relational Database / RDBMS?
โกSQL Data Types - Varchar, text, int, number, date, float, boolean.
โกSQL commands - select, where, like, distinct, between, group by, having, order by, insert into, case when, update, truncate, delete, commit, rollback (basically all the DDL, DML, DCL, TCL commands in SQL).
โกIntegrity Constraints - Primary key, foreign key, not null, unique.
โกOperators arithmetic, logical, and comparison operations.
โกUse of distinct, order by, limit, and top.
โกUse of union and union all.
โกJoins in SQL inner, left, right, outer, self, full outer, cross join.
Step 2 - Level up โฌโฌ :
โกNormalization in SQL
โกAggregate, date, and string functions
โกSub-Queries
โกCTE table / with clause
โกIn-built SQL functions
โกWindow functions
โกViews
Step 3 - Practice SQL Questions on leetcode & hackerrank โ
Hope it helps :)
I have divided this SQL roadmap into 3 steps (Basics, Level Up & Practice), and it should take around 1 month to complete.
Step 1 - Basics ๐ข :
โกWhat is a Relational Database / RDBMS?
โกSQL Data Types - Varchar, text, int, number, date, float, boolean.
โกSQL commands - select, where, like, distinct, between, group by, having, order by, insert into, case when, update, truncate, delete, commit, rollback (basically all the DDL, DML, DCL, TCL commands in SQL).
โกIntegrity Constraints - Primary key, foreign key, not null, unique.
โกOperators arithmetic, logical, and comparison operations.
โกUse of distinct, order by, limit, and top.
โกUse of union and union all.
โกJoins in SQL inner, left, right, outer, self, full outer, cross join.
Step 2 - Level up โฌโฌ :
โกNormalization in SQL
โกAggregate, date, and string functions
โกSub-Queries
โกCTE table / with clause
โกIn-built SQL functions
โกWindow functions
โกViews
Step 3 - Practice SQL Questions on leetcode & hackerrank โ
Hope it helps :)
๐6
Quick recap of essential SQL basics ๐๐
SQL is a domain-specific language used for managing and querying relational databases. It's crucial for interacting with databases, retrieving, storing, updating, and deleting data. Here are some fundamental SQL concepts:
1. Database
- A database is a structured collection of data. It's organized into tables, and SQL is used to manage these tables.
2. Table
- Tables are the core of a database. They consist of rows and columns, and each row represents a record, while each column represents a data attribute.
3. Query
- A query is a request for data from a database. SQL queries are used to retrieve information from tables. The SELECT statement is commonly used for this purpose.
4. Data Types
- SQL supports various data types (e.g., INTEGER, TEXT, DATE) to specify the kind of data that can be stored in a column.
5. Primary Key
- A primary key is a unique identifier for each row in a table. It ensures that each row is distinct and can be used to establish relationships between tables.
6. Foreign Key
- A foreign key is a column in one table that links to the primary key in another table. It creates relationships between tables in a database.
7. CRUD Operations
- SQL provides four primary operations for data manipulation:
- Create (INSERT) - Add new records to a table.
- Read (SELECT) - Retrieve data from one or more tables.
- Update (UPDATE) - Modify existing data.
- Delete (DELETE) - Remove records from a table.
8. WHERE Clause
- The WHERE clause is used in SELECT, UPDATE, and DELETE statements to filter and conditionally manipulate data.
9. JOIN
- JOIN operations are used to combine data from two or more tables based on a related column. Common types include INNER JOIN, LEFT JOIN, and RIGHT JOIN.
10. Index
- An index is a database structure that improves the speed of data retrieval operations. It's created on one or more columns in a table.
11. Aggregate Functions
- SQL provides functions like SUM, AVG, COUNT, MAX, and MIN for performing calculations on groups of data.
12. Transactions
- Transactions are sequences of one or more SQL statements treated as a single unit. They ensure data consistency by either applying all changes or none.
13. Normalization
- Normalization is the process of organizing data in a database to minimize data redundancy and improve data integrity.
14. Constraints
- Constraints (e.g., NOT NULL, UNIQUE, CHECK) are rules that define what data is allowed in a table, ensuring data quality and consistency.
Here is an amazing resources to learn & practice SQL: https://bit.ly/3FxxKPz
Share with credits: https://t.iss.one/sqlspecialist
Hope it helps :)
SQL is a domain-specific language used for managing and querying relational databases. It's crucial for interacting with databases, retrieving, storing, updating, and deleting data. Here are some fundamental SQL concepts:
1. Database
- A database is a structured collection of data. It's organized into tables, and SQL is used to manage these tables.
2. Table
- Tables are the core of a database. They consist of rows and columns, and each row represents a record, while each column represents a data attribute.
3. Query
- A query is a request for data from a database. SQL queries are used to retrieve information from tables. The SELECT statement is commonly used for this purpose.
4. Data Types
- SQL supports various data types (e.g., INTEGER, TEXT, DATE) to specify the kind of data that can be stored in a column.
5. Primary Key
- A primary key is a unique identifier for each row in a table. It ensures that each row is distinct and can be used to establish relationships between tables.
6. Foreign Key
- A foreign key is a column in one table that links to the primary key in another table. It creates relationships between tables in a database.
7. CRUD Operations
- SQL provides four primary operations for data manipulation:
- Create (INSERT) - Add new records to a table.
- Read (SELECT) - Retrieve data from one or more tables.
- Update (UPDATE) - Modify existing data.
- Delete (DELETE) - Remove records from a table.
8. WHERE Clause
- The WHERE clause is used in SELECT, UPDATE, and DELETE statements to filter and conditionally manipulate data.
9. JOIN
- JOIN operations are used to combine data from two or more tables based on a related column. Common types include INNER JOIN, LEFT JOIN, and RIGHT JOIN.
10. Index
- An index is a database structure that improves the speed of data retrieval operations. It's created on one or more columns in a table.
11. Aggregate Functions
- SQL provides functions like SUM, AVG, COUNT, MAX, and MIN for performing calculations on groups of data.
12. Transactions
- Transactions are sequences of one or more SQL statements treated as a single unit. They ensure data consistency by either applying all changes or none.
13. Normalization
- Normalization is the process of organizing data in a database to minimize data redundancy and improve data integrity.
14. Constraints
- Constraints (e.g., NOT NULL, UNIQUE, CHECK) are rules that define what data is allowed in a table, ensuring data quality and consistency.
Here is an amazing resources to learn & practice SQL: https://bit.ly/3FxxKPz
Share with credits: https://t.iss.one/sqlspecialist
Hope it helps :)
โค6๐2๐1