alex-kopylov / secret-hitler-telegram-bot-statistic-collector Goto Github PK

SELECT r.game_id,
       p.username,
       CASE 
           WHEN r.role IN ('HC', 'DH', 'HL', 'HW') THEN 'Hitler'
           WHEN r.role IN ('LW', 'LL') THEN 'Liberal'
           WHEN r.role IN ('FW', 'FL') THEN 'Fascist'
       END AS role
FROM records r
INNER JOIN players p ON r.player_id = p.id
WHERE r.game_id IN (
    SELECT game_id
    FROM records
    GROUP BY game_id
    HAVING SUM(CASE WHEN role IN ('HC', 'DH', 'HL', 'HW') THEN 1 ELSE 0 END) > 1
        OR SUM(CASE WHEN role IN ('LW', 'LL') THEN 1 ELSE 0 END) > 6
        OR SUM(CASE WHEN role IN ('FW', 'FL', 'HC', 'DH', 'HL', 'HW') THEN 1 ELSE 0 END) > 4
)
ORDER BY r.game_id;

SELECT DISTINCT r.game_id, p.username, 
    CASE 
        WHEN r.role IN ('LW', 'DH', 'FL', 'HL') THEN 'Liberal Win Condition'
        WHEN r.role IN ('FW', 'HW', 'HC', 'LL') THEN 'Fascist Win Condition'
    END AS win_condition
FROM records r
INNER JOIN players p ON r.player_id = p.id
WHERE r.game_id IN (
    SELECT game_id FROM records WHERE role IN ('LW', 'DH', 'FL', 'HL')
    INTERSECT
    SELECT game_id FROM records WHERE role IN ('FW', 'HW', 'HC', 'LL')
)
ORDER BY r.game_id;