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

CodePudding user response：

• Join to orders
  • Get only those over 30 years old and with orders from last 6 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 BETWEEN DATE_ADD(LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 6 1 MONTH)), INTERVAL 1 DAY)
                         AND LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))
GROUP BY usr.Name
HAVING COUNT(ord.order_id) >= 3
LIMIT 1

Test code for the date calculations

-- previous month, last day
select LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))

| LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) |
| :---------------------------------------------- |
| 2021-10-31                                      |

-- 6 months ago, first day
select DATE_ADD(LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 6 1 MONTH)), INTERVAL 1 DAY)
  

| DATE_ADD(LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 6 1 MONTH)), INTERVAL 1 DAY) |
| :-------------------------------------------------------------------------- |
| 2021-05-01                                                                  |

-- someone's current age
select TIMESTAMPDIFF(YEAR, '2005-11-28', CURDATE())

| TIMESTAMPDIFF(YEAR, '2005-11-28', CURDATE()) |
| -------------------------------------------: |
|                                           15 |

db<>fiddle here