nanodeg_p6_capstone/sql_queries.py at main · lastnode/nanodeg_p6_capstone · GitHub

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
"""
Support module for the ETL scripts.
Contains the queries used by the following functions:

"""


games_table_chessdotcom = ("""

    select
        from_unixtime(end_time, 'yyyy-MM-dd hh:mm:ss') as game_end_time,
        from_unixtime(end_time, 'yyyy-MM-dd') as game_end_date,
        time_class,
        rated,
        white_username,
        white_rating,
        black_username,
        black_rating,
        case
            when white_result = "win" then "white"
            when black_result = "win" then "black"
            when (white_result = "agree" or black_result = "agree") then "draw"
            when (white_result = "stalemate" or black_result = "stalemate") then "stalemate"
        end as winner,
        get_termination_from_pgn(pgn) as termination,
        get_eco_from_pgn(pgn) as opening,
        get_moves_from_pgn(pgn) as moves,
        'chessdotcom' as platform
    from chessdotcom_staging
    order by game_end_time

""")


games_table_lichess = ("""

	select
	    from_unixtime(lastMoveAt/1000, 'yyyy-MM-dd hh:mm:ss') as game_end_time,
	    from_unixtime(lastMoveAt/1000, 'yyyy-MM-dd') as game_end_date,
	    speed as time_class,
	    players_white_user_name as white_username,
	    players_white_rating as white_rating,
	    players_black_user_name as black_username,
	    players_black_rating as black_rating,
	    case
	        when winner = "black" then "black"
	        when winner = "white" then "white"
	        when winner = "" then status
	    end as winner,
	    status as termination,
	    opening_name as opening,
	    moves,
	    'lichess' as platform
	from lichess_staging

	order by game_end_time

""")