SQL Questions Pattern - Part 2

Hey all! A few days back I gave a set of SQL questions that most likely will be asked in every interview. This post is the continuation of it. Today let's cover up the rest of the SQL questions pattern that will be asked frequently.

1. This question is more about finding the user session that is concurrent with the largest number of other user sessions. Such concurrent questions are termed to be asked a difficult questions.

SOLUTION:

SELECT
    s1.session_id, 
    COUNT(s2.session_id) AS concurrents
FROM 
    sessions s1
    JOIN sessions s2 ON s1.session_id!=s2.session_id
    AND s2.start_time BETWEEN s1.start_time AND s1.end_time 
GROUP BY 
    s1.session_id
ORDER_BY 
    concurrents DESC
LIMIT 1

2. This question is mostly asked to test your knowledge on how to apply separation between entries that are even or odd. The questions can be a little tweaked by the idea is the same.

SOLUTION:

WITH measuremets_by_count AS (
    SELECT
        CAST(measurement_time AS date) measurement_date,
        measurement_value,
        ROW_NUMBER() OVER (PARTITION BY CAST(measurement_time AS date)
        ORDER BY measurement_time ASC) AS measurement_count
    FROM 
        measurements
)
SELECT 
    measurement_day,
    SUM(IF(measurement_count%2!=0, 0, measurement_value)) AS odd_sum,
    SUM(IF(measurement_count%2==0, 0, measurement_value)) AS even_sum
FROM
    measurements_by_count
GROUP BY 
    measurement_day
ORDER BY 
    measurement_day ASC

3. This question is most frequently asked in many interviews. In fact, I have faced them as well.

SOLUTION:

WITH (
SELECT
    t.user_id,
    p.product_name,
    t.transaction_id
FROM 
    transactions t
    JOIN product p ON t.product_id = p.product_id
) AS purchase_info

SELECT 
    p1.product_name AS product1,
    p2.product_name AS product2,
    COUNT(*) AS count
FROM 
    purchase_info p1
    JOIN purchase_info p2 ON p1.transaction_id = p2.transaction_id
    AND p1.product_id < p2.product_id
GROUP BY 1,2 
ORDER BY 5 DESC
LIMIT 10

4. These WINDOW functions are most likely askely to test your knowledge on how you are aware of using LEAD() and LAG() functions.

SOLUTION:

SEELCT 
    inspection_date::DATE,
    COUNT(violation_id),
    LAG(COUNT(violation_id)) OVER(ORDER BY inspection_date::DATE), 
    COUNT(violation_id)-LAG(COUNT(violation_id)) OVER(ORDER BY inspection_date::DATE) as diff
FROM 
    sf_restaurant_health_violaions 
GROUP BY 1

if above question asked to calculste the difference between the count of current and next violations by inspection datw. it could be solved using LEAD() function as follows:

SELECT 
    inspection_date::DATE.
    COUNT(violation_id),
    LEAD(COUNT(violation_id)) OVER(ORDER BY inspection_date::DATE), 
    COUNT(violation_id)-LEAD(COUNT(violation_id)) OVER(ORDER BY inspection_date::DATE) as diff
FROM 
    sf_restaurant_health_violaions 
GROUP BY 1

That's all for the most likely asked SQL question pattern. I hope this will help us in your next coming interviews and please like, share, and subscribe to this blog for more data science-related posts. Happy Learning!!