I have a table of users and a table of orders. Table data is linked using a key
user_id. The user has a date of birth. It is necessary to compose a query to display one random user from the users table, over 30 years old, who has made at least 3 orders in the last six months.

I was able to make a query to sample by age:

SELECT Name from users WHERE(DATEDIFF(SYSDATE(), birthday_at)/365)>30;

but I don’t know how to solve the problem to the end

>Solution :

• Join to orders
  • Get only those over 30 years old and with orders from last 3 months
    • Group by the user
      • Filter on the count with a having
        • Limit to 1 without sorting (since random)

SELECT usr.Name AS UserName
FROM users AS usr
JOIN orders AS ord
  ON ord.user_id = usr.user_id
WHERE TIMESTAMPDIFF(YEAR, usr.birthday_at, CURDATE()) > 30
  AND ord.order_date >= DATE_ADD(LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 3+1 MONTH)), INTERVAL 1 DAY)
GROUP BY usr.Name
HAVING COUNT(ord.order_id) >= 3
LIMIT 1