I can not compose a query to the database

i have 2 tables, the first one is “users” with columns id, name, birthday_at, created_at, updated_at. The second is “orders” with columns id, user_id, created_at, updated_at.

i need “Display one random user from the shop.users table, older than 30,who made at least 3 orders in the last six months”. my request is :

  "SELECT * FROM users
    WHERE YEAR(CURDATE()) - YEAR(birthday_at) > 30
    AND
    (SELECT COUNT(*) FROM orders WHERE id = orders.user_id )>= 3
    ORDER BY RAND() LIMIT 1;"

but it not works. can i ask you for help?

Answer

You can try this query, I included the comment to clarify

SELECT * FROM users
    WHERE YEAR(CURDATE()) - YEAR(birthday_at) > 30  # the first condition : older than 30
  AND id IN                                         # the second condition : more than 3 orders
    (SELECT orders.user_id AS id                    # here, we count order of user in orders table, using GROUP BY
        FROM orders
        WHERE MONTH(CURDATE()) - MONTH(created_at) <= 6
        GROUP BY orders.user_id         
        HAVING COUNT(*) >=3                         # condition : >= 3 orders
    )
    ORDER BY RAND() LIMIT 1                         # get random user, you are correct