0
User
|uid|admin|match_id|code|
|152|0    |NULL    |PH     |
|66 |1    |2212    |US     |
|66 |0    |1234    |AL     |
|66 |0    |2221    |AL     |


Transfer
|eid|transfer_at|match_id|
|284|2018-11-29 |2001    |
|284|2018-11-30 |2023    |
|284|2018-12-03 |2023    |

When two users are transferred together, they become a 'transfer'. Every transfer has TWO users. One user can have multiple matches.

When two users complete a transfer, a row is added to the transfer table.

Result Expected - codes where the average number of transfer among users in that code is less than two

|code|
|PH     |
|US     |
|BR     |
4212extra
  • 81
  • 1
  • 7
  • Tables and sample data should be shown as `CREATE TABLE` and `INSERT INTO` statements, not as images. Desired result and the query you've tried so far are also missing. See: [Example] – sticky bit Dec 18 '19 at 01:25
  • You should only tag relevant DBMS. I removed the tags. Please add only the one of your DBMS. – sticky bit Dec 18 '19 at 01:25
  • And there's no question here. – sticky bit Dec 18 '19 at 01:26
  • Does this answer your question? [How do I query for all dates greater than a certain date in SQL Server?](https://stackoverflow.com/questions/10643379/how-do-i-query-for-all-dates-greater-than-a-certain-date-in-sql-server) – 4212extra Dec 18 '19 at 19:43

1 Answers1

1

First left join exchange to user and use aggregation to get the number of matches per user. Then aggegate again to get the average number of matches per country.

SELECT x.country
       FROM (SELECT u.uid,
                    u.country,
                    count(e.eid) matches
                    FROM user u
                         LEFT JOIN exchange e
                                   ON e.match_id = u.match_id
                    GROUP BY u.uid,
                             u.country) x
       GROUP BY x.country
       HAVING avg(x.matches) < 2;
sticky bit
  • 36,626
  • 12
  • 31
  • 42