SQL Questions Pattern - Part 1

Hi Everyone, Hope everyone is doing well. While preparing for any interviews, it is noted that if we know what is the pattern of questions asked then there is a way to solve the problem.

This is true for any subject be it solving maths problems, coding questions, and most sought-after “SQL” questions. A few days back on LinkedIn, I posted a set of SQL interview questions that are most likely asked in any interviews.

Following these patterns, facing SQL interview questions is easy.

Below are the solution are the questions posted in my Github repo

Solution 1:

SELECT 
    u.city,
    COUNT(DISTINCT t.order_id) AS num_orders
FROM 
    trades t
    JOIN users u ON t.user_id = u.user_id 
WHERE 
    t.status = 'complete'
GROUP BY 
    city
ORDER BY 
    num_orders DESC 
LIMIT 3

Solution 2:

SELECT 
    trans_date,
    product_id,
    SUM(spend) OVER (PARTITION BY product_id ORDER BY trans_date) AS cum_spend
FROM 
    total_trans 
ORDER BY product_id, trans_date ASC
    

Solution 3:

SELECT 
    num_tweets AS tweet_bucket,
    COUNT(*) AS num_users
    FROM 
    (
        SELECT 
            user_id, 
            COUNT(*) AS num_tweets
        FROM
            tweets
        WHERE 
            tweet_date BETWEEN '2020-01-01' AND '2020-12-31'
        GROUP BY 
            user_id
        ) total_tweets
     GROUP BY 
            num_tweets
     ORDER BY 
            num_tweets ASC

Solution 4:

WITH purchase_num AS (
    SELECT
        user_id, 
        spend,
    ROW_NUMBER() OVER() ( PARTITION BY user_id ORDER BY transaction_date ASC) as rownum
    FROM 
        user_transactions u 
     
        
SELECT 
    user_id
FROM 
    purchase_num 
WHERE 
    rownum = 1 AND spend >= 50.00

Solution 5:

WITH tweet_counts AS (
    SELECT 
        user_id,
        CAST(tweet_date AS date) AS tweet_date,
        COUNT(*) AS num_tweets
    FROM
        tweets 
    GROUP BY user_id, 
        CAST(tweet_date AS date)
)
   
SELECT
    user_id, 
    tweet_date, 
    AVG(num_tweets) OVER(PARTITION BY user_id ORDER BY user_id, tweet_date ROWS BETWEEN 6 preceding AND CURRENT ROW) AS rolling_avg_7d
FROM 
    tweet_counts
         

These are the first set of questions and next set of questions will be given in the following days.

Hope you like this blog and please like, share, and subscribe to this blog for more data science related posts.