graphs/sql/following_you/req04.sql


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

SELECT
    place_type,
    entries,
    place_id
FROM
    (
        (
            SELECT
                'metro' AS place_type,
                station_id AS place_id,
                COUNT(DISTINCT person_id) AS entries
            FROM transport.metro_usage_logs
            GROUP BY station_id
        )
        UNION
        (
            SELECT
                'shop' AS place_type,
                shop_id AS place_id,
                COUNT(DISTINCT person_id) AS entries
            FROM public.shop_entrance_logs
            GROUP BY shop_id
        )
    ) AS res
ORDER BY entries DESC, place_id ASC, place_type DESC