In the ever-evolving landscape of technology and business, data analytics has emerged as a critical tool for companies to derive insights, make informed decisions, and stay ahead of the curve. Aspiring data analysts aiming for a position at Meesho or any tech-driven company must be prepared to showcase their skills and knowledge during the interview process. Here, we delve into some common data analytics interview questions and provide insights into how to answer them effectively.
Table of Contents
SQL queries
- Retrieve Product Information
Question: How would you retrieve all products with their names and prices?
Answer:
SELECT product_name, price FROM products;
- Count Number of Orders
Question: How do you count the total number of orders placed?
Answer:
SELECT COUNT(order_id) AS total_orders FROM orders;
- Joining Tables
Question: Explain how you would join the orders and customers tables to get customer names for each order.
Answer:
SELECT o.order_id, c.customer_name FROM orders o JOIN customers c ON o.customer_id = c.customer_id;
- Retrieve Latest Orders
Question: How do you get the details of the latest 10 orders placed?
Answer:
(Assuming order_date is the timestamp for the orders):
SELECT * FROM orders ORDER BY order_date DESC LIMIT 10;
- Calculate Total Revenue
Question: Can you write a query to calculate the total revenue generated from orders?
Answer:
(Assuming order_total is the total amount for each order):
SELECT SUM(order_total) AS total_revenue FROM orders;
Question: Retrieve orders placed between January 1, 2023, and December 31, 2023.
Answer:
SELECT * FROM orders WHERE order_date BETWEEN ‘2023-01-01’ AND ‘2023-12-31’;
- Get Average Order Value
Question: Calculate the average value of orders.
Answer:
(Assuming order_total is the total amount for each order):
SELECT AVG(order_total) AS average_order_value FROM orders;
- Retrieve Customers with Multiple Orders
Question: Find customers who have placed more than 3 orders.
Answer:
SELECT customer_id, COUNT(order_id) AS total_orders FROM orders GROUP BY customer_id HAVING total_orders > 3;
- Update Product Prices
Question: How would you increase the price of all products by 10%?
Answer:
UPDATE products SET price = price * 1.10;
Question: What is a Union in SQL?
Answer: UNION in SQL is a set operator that combines the result sets of two or more SELECT statements into one.
It removes duplicates by default and requires that the SELECT statements have the same number of columns.
Syntax: SELECT column1, column2 FROM table1 UNION SELECT column1, column2 FROM table2;
It’s commonly used to merge data from different tables with similar structures or to combine results from multiple queries into a single result set.
Question: What is Joins in SQL?
Answer: SQL joins are used to combine rows from two or more tables based on related columns. INNER JOIN returns rows with matching values in both tables. LEFT JOIN returns all rows from the left table and matching rows from the right table, with NULL values where there’s no match. RIGHT JOIN is similar, but it returns all rows from the right table. FULL JOIN returns all rows when there’s a match in either table, with NULLs where there’s no match. These joins help create powerful queries that fetch data from different tables based on their relationships.
SQL query on rank and window function
Question: What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER() in SQL?
Answer:
- RANK(): Assigns a unique rank to each distinct row, leaving gaps in rank numbers for rows with the same value.
- DENSE_RANK(): Similar to RANK(), but does not leave gaps in rank numbers for rows with the same value.
- ROW_NUMBER(): Assigns a unique sequential integer to each row within the partition of a result set, without any gaps.
Question: Explain the concept of a window function in SQL.
Answer: A window function performs a calculation across a set of rows related to the current row, but without collapsing the result set into a single value. It allows you to perform aggregate functions like SUM(), AVG(), MAX(), MIN() over a specified window or subset of rows.
Question: Explain the PARTITION BY clause in window functions.
Answer: The PARTITION BY clause divides the result set into partitions, and the window function is applied to each partition separately. It allows you to perform calculations and ranking within each partition independently.
Question: Practical Use of LEFT JOIN.
Answer: Retrieving Unmatched Records: Use LEFT JOIN to find records in the left table that do not have corresponding matches in the right table.
Including All Records from Left Table: Ensures all rows from the left table are included in the result set, even if there are no matches in the right table.
Useful in Reporting: Commonly used in generating reports to show all data from one table, with related data from another table where available.
Handling Optional Relationships: Ideal for scenarios where you want to include optional related data without excluding main records.
Question: Explain Vlookup in excel.
Answer: VLOOKUP in Excel searches for a value in the first column of a table or range.
It then returns a corresponding value from a specified column in the same row.
Syntax: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Used for finding specific information in large datasets, like prices, IDs, or grades.
Example: =VLOOKUP(“Apple”, A2:B10, 2, FALSE) retrieves the value corresponding to “Apple” in column A.
Ideal for quick data retrieval and simplifying data analysis tasks in Excel.
Question: What are the some functions in sql
Answer:
Aggregate Functions:
- COUNT(): Counts the number of rows in a result set.
- SUM(): Calculates the sum of values in a column.
- AVG(): Calculates the average of values in a column.
- MIN(): Finds the minimum value in a column.
- MAX(): Finds the maximum value in a column.
String Functions:
- CONCAT(): Concatenates two or more strings together.
- SUBSTRING(): Extracts a substring from a string.
- UPPER(): Converts a string to uppercase.
- LOWER(): Converts a string to lowercase.
- LENGTH(): Returns the length of a string.
Date and Time Functions:
- NOW(): Returns the current date and time.
- DATE(): Extracts the date part from a datetime expression.
- YEAR(): Extracts the year from a date or datetime expression.
- MONTH(): Extracts the month from a date or datetime expression.
- DAY(): Extracts the day of the month from a date or datetime expression.
Mathematical Functions:
- ROUND(): Rounds a number to a specified number of decimal places.
- ABS(): Returns the absolute value of a number.
- POWER(): Raises a number to a specified power.
- SQRT(): Calculates the square root of a number.
Window Functions:
- ROW_NUMBER(): Assigns a unique sequential integer to each row.
- RANK(): Assigns a unique rank to each distinct row.
- DENSE_RANK(): Assigns a unique rank without gaps to each distinct row.
- LEAD(): Accesses data from a subsequent row.
- LAG(): Accesses data from a previous row.
Other topics
- Most of the questions are based on self-join
- SQL queries, joins, union
Conclusion
Preparing for data analytics interviews requires not only technical proficiency but also the ability to articulate your thought process, problem-solving skills, and adaptability to new challenges. By understanding these common questions and formulating thoughtful responses, aspiring data analysts can confidently navigate the interview process and demonstrate their value to companies like Meesho.
In the dynamic world of data analytics, continuous learning, staying updated with industry trends, and hands-on experience with real-world projects are key to success. Embracing curiosity, analytical thinking, and a passion for turning data into actionable insights will undoubtedly pave the way for a rewarding career in this exciting field.
