Takeaways from Solving 40 Easy Questions on Interview Query

2 interesting problems at the end!!!!

Introduction:

As I aim to achieve a data analyst internship for the summer of 2025 with a target pay of at least $35 per hour, I’m continually honing my SQL skills. Recently, I tackled 40 easy SQL questions on Interview Query and came out with a treasure trove of insights. Let’s dive into my key takeaways, peppered with practical examples, common pitfalls, and fun anecdotes.

Photo by Elimende Inagella on Unsplash

1. The Limit and Offset Pair:
Ah, the dynamic duo of SQL querying! Limit and Offset are like the bread and butter for paginating query results.

Example:
SELECT * FROM customers LIMIT 10 OFFSET 20;


--This query fetches 10 rows starting from the 21st row. 
--It’s incredibly handy for breaking down large datasets into manageable chunks.

Pitfall: Forgetting that `OFFSET` starts from 0, not 1. Remember, SQL is a zero-based indexing language!

2. IN and NOT IN Operator:
These operators are the unsung heroes of SQL, making it easy to filter rows based on a set of values.

SELECT name FROM students WHERE grade IN (‘A’, ‘B’, ‘C’);
SELECT name FROM students WHERE grade NOT IN (‘D’, ‘F’);

--IN and NOT IN are great for filtering, but be cautious with large sets, as performance can take a hit.

3. IFNULL in SELECT Statement:
IFNULL is like the Swiss Army knife for dealing with NULL values.

SELECT name, IFNULL(phone, ‘N/A’) AS phone_number FROM contacts;

This replaces NULL phone numbers with ‘N/A’. It’s a lifesaver when dealing with incomplete data.

Pitfall: Using IFNULL with incompatible data types can lead to errors. Ensure the replacement value matches the column’s data type.

4. DATEDIFF Function:
Ever needed to calculate the difference between two dates? DATEDIFF has got you covered.

SELECT DATEDIFF(end_date, start_date) AS duration FROM projects;

--This returns the number of days between the start and end dates of projects.

Tip: Be mindful of the date format your database uses to avoid discrepancies.

5. Group By and Having Clause:
GROUP BY is like organizing your wardrobe by color, and HAVING is the strict parent ensuring everything stays neat.

SELECT department, COUNT(*) AS num_employees 
FROM employees 
GROUP BY department 
HAVING num_employees > 10;

--This groups employees by department and only returns those with more than 10 employees.

Pitfall: Mixing up WHERE and HAVING. Remember, WHERE filters rows before grouping, and HAVING filters groups after.

6. The WITH Clause:
Common Table Expressions (CTEs) introduced by the WITH clause make complex queries easier to manage.

WITH department_counts AS (
 SELECT department, COUNT(*) AS num_employees FROM employees GROUP BY department
)
SELECT * FROM department_counts WHERE num_employees > 10;

CTEs improve readability and maintainability, especially for multi-step queries.

7. DENSE_RANK() and RANK():
Ranking functions are perfect for generating ordered lists with ties.

SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rank FROM contestants;
SELECT name, score, DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank FROM contestants;

--RANK() leaves gaps in the ranking sequence for ties, while DENSE_RANK() does not.

Tip: Choose DENSE_RANK() if you want sequential ranks without gaps.

8. BETWEEN for Dates:
BETWEEN is ideal for filtering records within a specific range.

SELECT * FROM sales WHERE sale_date BETWEEN ‘2023–01–01’ AND ‘2023–12–31’;

--This retrieves all sales within the year 2023.

Pitfall: Remember that BETWEEN is inclusive. To exclude endpoints, use a combination of > and < operators.

9. TIMESTAMPDIFF:
TIMESTAMPDIFF is great for calculating the difference between two timestamps.

SELECT TIMESTAMPDIFF(HOUR, start_time, end_time) AS duration_hours FROM work_logs;

--This calculates the difference in hours between start and end times.

Tip: Ensure both timestamps are in the same time zone to avoid inaccuracies.

2 Problems I loved Solving

Question 1

with before_rankings as (
    select action, count(*) as ranking from events
    where platform in ('iphone', 'ipad') AND year(created_at) = 2020 and
        month(created_at) = 11
    group by 1
)

select action, dense_rank() over (order by ranking desc) as ranks from before_rankings
limit 5

From this SQL query, we can see the power of Common Table Expressions (CTEs) for simplifying complex queries. The CTE `before_rankings` aggregates event counts by action for a specific month and year on iPhone and iPad platforms. Using `DENSE_RANK()` over the aggregated results, I can rank the actions by their frequency in descending order. This query highlights the importance of structuring queries for clarity and efficiency, making them easier to understand and debug. It also underscores the practical use of window functions like `DENSE_RANK()` to generate ordered lists based on specified criteria.

Question 2

WITH row_nums AS 
(
  SELECT *, 
    ROW_NUMBER() OVER (PARTITION BY product_category ORDER BY id) AS row_no 
  FROM purchases 
)

SELECT row_nums.product_name AS product_name,  
IF(row_no = 1, 0, 1) AS category_previously_purchased 
FROM row_nums
ORDER BY id;

From this SQL query, I learned how to use the `ROW_NUMBER()` window function to assign sequential integers to rows within each product category, effectively partitioning the data. The Common Table Expression (CTE) `row_nums` calculates these row numbers and associates them with each product in the purchases table. By leveraging the `IF` statement in the final `SELECT` clause, we can flag whether a product category has been previously purchased (`0` for the first occurrence and `1` for subsequent ones). This approach helps in analyzing purchase patterns and identifying first-time purchases within each category. The query also illustrates the practical application of window functions and conditional logic to gain insights from transactional data.

Conclusion:

Solving 40 easy SQL questions was like navigating through a maze with delightful surprises at every turn. From the pragmatic LIMIT and OFFSET pair to the meticulous DENSE_RANK() and RANK(), each concept enriched my SQL prowess. As I gear up for my data analyst internship, these learnings are invaluable. Remember, practice makes perfect. So, grab your SQL toolkit and start querying away! Who knows, you might discover some hidden gems of your own. Happy querying!