SQL Interview Questions – Market Analysis I

Spread the love

Problem Description –

Write an SQL query to find for each user, the join date and the number of orders they made as a buyer in 2019.

Return the result table in any order.

The query result format is in the following example.

Difficulty Level – Medium

Problem Link – Market Analysis I

Solution –

    user_id as buyer_id,
    IFNULL(orders_in_2019, 0) as orders_in_2019
FROM Users as u LEFT JOIN 
        COUNT(o.order_id) as orders_in_2019
    FROM Orders as o JOIN Users as u
    ON o.buyer_id = u.user_id
    WHERE YEAR(order_date) = '2019'
    GROUP BY 1) x
ON u.user_id = x.buyer_id
ORDER by 1

Rating: 1 out of 5.

Leave a Reply