How to write a Sql query to get data from two tables?

Question

SQL

There are 2 tables:
users (id, name, date), date of birth in unixtime
stat format (id, id_user, id_stat)
You need to make a query that returns the name and number of articles of users aged 16 to 18.

It turns out, let's say Misha is 16 years old, he has 2 articles, then Misha's conclusion is 2

Answer 1

If strictly according to the conditions, then so:

SELECT
  u.name, (SELECT COUNT(*) FROM stat s WHERE s.id_user = u.id)
FROM users u
WHERE u.date BETWEEN :dt1 AND :dt2

So you will count the articles only of those users who have them and it is convenient to add conditions to the articles themselves:

SELECT u.name, count(*)
FROM
  stat s
    JOIN users u ON u.id = s.id_user AND u.date BETWEEN :dt1 AND :dt2
GROUP BY u.id, u.name

Can be turned inside out. This will also return zero articles for users without articles. As in the first query, but you can aggregate in addition to the number of articles, say, the largest number of likes (if the article had another number of likes). Or the latest article (if the article had a publication date). The first request is not capable of this, or rather it is capable, but at the expense of either additional costs or tricks and not in all dialects.

SELECT u.name, count(*)
FROM
  users u  
    LEFT JOIN stat s ON s.id_user = u.id
WHERE u.date BETWEEN :dt1 AND :dt2
GROUP BY u.id, u.name