DAY 01 - SQL QUERIES

 

Question

You have a table called orders:

orders(order_id, user_id, amount, order_date)

Write an SQL query to find all users who have placed at least one order greater than the average order amount.

Answer

SELECT user_id

FROM orders

WHERE amount > (

    SELECT AVG(amount)

    FROM orders

);

To avoid duplicate users:

SELECT DISTINCT user_id

FROM orders

WHERE amount > (

    SELECT AVG(amount)

    FROM orders

);

Explanation: Inner query calculates the average order amount first, then the outer query finds users with orders above that average.