How to make such sql query?

Question

SQL

Travel agency theme. People fly to different countries.
Question : how to withdraw those who have been to " 2 countries at least twice "?
There is a table "order" in which fields, for example, date, country, client. How to make such requests? Thanks in advance!

Answer 1

For MySQL it would be something like this:

SELECT `client_id`
    FROM (
        SELECT `client_id`, `country_id`
            FROM `orders`
            GROUP BY `client_id`, `country_id`
            HAVING COUNT(*) > 1
    ) AS `t1`
    GROUP BY `client_id`
    HAVING COUNT(*) > 1

In MSSQL, the principle will be the same, but there may be some peculiarities.

Answer 2

with cl ( contry,client,koll) as
(select contry,client ,COUNT(*) over (partition by client,contry)
from orders
)
select distinct * from cl
where koll >=2
on MSSQL you can try like this