================================================================================ L2 DATABASE COMPLETE SCHEMA ================================================================================ TABLE: dim_maps Rows: 9 | Columns: 3 -------------------------------------------------------------------------------- CREATE TABLE dim_maps ( map_id INTEGER PRIMARY KEY AUTOINCREMENT, map_name TEXT UNIQUE NOT NULL, map_desc TEXT ); COLUMNS: map_id INTEGER [PK] map_name TEXT NOT NULL map_desc TEXT TABLE: dim_players Rows: 1,181 | Columns: 68 -------------------------------------------------------------------------------- CREATE TABLE dim_players ( steam_id_64 TEXT PRIMARY KEY, uid INTEGER, -- 5E Platform ID username TEXT, avatar_url TEXT, domain TEXT, created_at INTEGER, -- Timestamp updated_at INTEGER, -- Timestamp last_seen_match_id TEXT, uuid TEXT, email TEXT, area TEXT, mobile TEXT, user_domain TEXT, username_audit_status INTEGER, accid TEXT, team_id INTEGER, trumpet_count INTEGER, profile_nickname TEXT, profile_avatar_audit_status INTEGER, profile_rgb_avatar_url TEXT, profile_photo_url TEXT, profile_gender INTEGER, profile_birthday INTEGER, profile_country_id TEXT, profile_region_id TEXT, profile_city_id TEXT, profile_language TEXT, profile_recommend_url TEXT, profile_group_id INTEGER, profile_reg_source INTEGER, status_status INTEGER, status_expire INTEGER, status_cancellation_status INTEGER, status_new_user INTEGER, status_login_banned_time INTEGER, status_anticheat_type INTEGER, status_flag_status1 TEXT, status_anticheat_status TEXT, status_flag_honor TEXT, status_privacy_policy_status INTEGER, status_csgo_frozen_exptime INTEGER, platformexp_level INTEGER, platformexp_exp INTEGER, steam_account TEXT, steam_trade_url TEXT, steam_rent_id TEXT, trusted_credit INTEGER, trusted_credit_level INTEGER, trusted_score INTEGER, trusted_status INTEGER, trusted_credit_status INTEGER, certify_id_type INTEGER, certify_status INTEGER, certify_age INTEGER, certify_real_name TEXT, certify_uid_list TEXT, certify_audit_status INTEGER, certify_gender INTEGER, identity_type INTEGER, identity_extras TEXT, identity_status INTEGER, identity_slogan TEXT, identity_list TEXT, identity_slogan_ext TEXT, identity_live_url TEXT, identity_live_type INTEGER, plus_is_plus INTEGER, user_info_raw TEXT ); COLUMNS: steam_id_64 TEXT [PK] uid INTEGER username TEXT avatar_url TEXT domain TEXT created_at INTEGER updated_at INTEGER last_seen_match_id TEXT uuid TEXT email TEXT area TEXT mobile TEXT user_domain TEXT username_audit_status INTEGER accid TEXT team_id INTEGER trumpet_count INTEGER profile_nickname TEXT profile_avatar_audit_status INTEGER profile_rgb_avatar_url TEXT profile_photo_url TEXT profile_gender INTEGER profile_birthday INTEGER profile_country_id TEXT profile_region_id TEXT profile_city_id TEXT profile_language TEXT profile_recommend_url TEXT profile_group_id INTEGER profile_reg_source INTEGER status_status INTEGER status_expire INTEGER status_cancellation_status INTEGER status_new_user INTEGER status_login_banned_time INTEGER status_anticheat_type INTEGER status_flag_status1 TEXT status_anticheat_status TEXT status_flag_honor TEXT status_privacy_policy_status INTEGER status_csgo_frozen_exptime INTEGER platformexp_level INTEGER platformexp_exp INTEGER steam_account TEXT steam_trade_url TEXT steam_rent_id TEXT trusted_credit INTEGER trusted_credit_level INTEGER trusted_score INTEGER trusted_status INTEGER trusted_credit_status INTEGER certify_id_type INTEGER certify_status INTEGER certify_age INTEGER certify_real_name TEXT certify_uid_list TEXT certify_audit_status INTEGER certify_gender INTEGER identity_type INTEGER identity_extras TEXT identity_status INTEGER identity_slogan TEXT identity_list TEXT identity_slogan_ext TEXT identity_live_url TEXT identity_live_type INTEGER plus_is_plus INTEGER user_info_raw TEXT TABLE: fact_match_players Rows: 2,080 | Columns: 101 -------------------------------------------------------------------------------- CREATE TABLE fact_match_players ( match_id TEXT, steam_id_64 TEXT, team_id INTEGER, -- 1 or 2 -- Basic Stats kills INTEGER DEFAULT 0, deaths INTEGER DEFAULT 0, assists INTEGER DEFAULT 0, headshot_count INTEGER DEFAULT 0, kd_ratio REAL, adr REAL, rating REAL, -- 5E Rating rating2 REAL, rating3 REAL, rws REAL, mvp_count INTEGER DEFAULT 0, elo_change REAL, rank_score INTEGER, is_win BOOLEAN, -- Advanced Stats (VIP/Plus) kast REAL, entry_kills INTEGER, entry_deaths INTEGER, awp_kills INTEGER, clutch_1v1 INTEGER, clutch_1v2 INTEGER, clutch_1v3 INTEGER, clutch_1v4 INTEGER, clutch_1v5 INTEGER, flash_assists INTEGER, flash_duration REAL, jump_count INTEGER, -- Utility Usage Stats (Parsed from round details) util_flash_usage INTEGER DEFAULT 0, util_smoke_usage INTEGER DEFAULT 0, util_molotov_usage INTEGER DEFAULT 0, util_he_usage INTEGER DEFAULT 0, util_decoy_usage INTEGER DEFAULT 0, damage_total INTEGER, damage_received INTEGER, damage_receive INTEGER, damage_stats INTEGER, assisted_kill INTEGER, awp_kill INTEGER, awp_kill_ct INTEGER, awp_kill_t INTEGER, benefit_kill INTEGER, day TEXT, defused_bomb INTEGER, end_1v1 INTEGER, end_1v2 INTEGER, end_1v3 INTEGER, end_1v4 INTEGER, end_1v5 INTEGER, explode_bomb INTEGER, first_death INTEGER, fd_ct INTEGER, fd_t INTEGER, first_kill INTEGER, flash_enemy INTEGER, flash_team INTEGER, flash_team_time REAL, flash_time REAL, game_mode TEXT, group_id INTEGER, hold_total INTEGER, id INTEGER, is_highlight INTEGER, is_most_1v2 INTEGER, is_most_assist INTEGER, is_most_awp INTEGER, is_most_end INTEGER, is_most_first_kill INTEGER, is_most_headshot INTEGER, is_most_jump INTEGER, is_svp INTEGER, is_tie INTEGER, kill_1 INTEGER, kill_2 INTEGER, kill_3 INTEGER, kill_4 INTEGER, kill_5 INTEGER, many_assists_cnt1 INTEGER, many_assists_cnt2 INTEGER, many_assists_cnt3 INTEGER, many_assists_cnt4 INTEGER, many_assists_cnt5 INTEGER, map TEXT, match_code TEXT, match_mode TEXT, match_team_id INTEGER, match_time INTEGER, per_headshot REAL, perfect_kill INTEGER, planted_bomb INTEGER, revenge_kill INTEGER, round_total INTEGER, season TEXT, team_kill INTEGER, throw_harm INTEGER, throw_harm_enemy INTEGER, uid INTEGER, year TEXT, sts_raw TEXT, level_info_raw TEXT, PRIMARY KEY (match_id, steam_id_64), FOREIGN KEY (match_id) REFERENCES fact_matches(match_id) ON DELETE CASCADE -- Intentionally not enforcing FK on steam_id_64 strictly to allow stats even if player dim missing, but ideally it should match. ); COLUMNS: match_id TEXT [PK] steam_id_64 TEXT [PK] team_id INTEGER kills INTEGER DEFAULT 0 deaths INTEGER DEFAULT 0 assists INTEGER DEFAULT 0 headshot_count INTEGER DEFAULT 0 kd_ratio REAL adr REAL rating REAL rating2 REAL rating3 REAL rws REAL mvp_count INTEGER DEFAULT 0 elo_change REAL rank_score INTEGER is_win BOOLEAN kast REAL entry_kills INTEGER entry_deaths INTEGER awp_kills INTEGER clutch_1v1 INTEGER clutch_1v2 INTEGER clutch_1v3 INTEGER clutch_1v4 INTEGER clutch_1v5 INTEGER flash_assists INTEGER flash_duration REAL jump_count INTEGER util_flash_usage INTEGER DEFAULT 0 util_smoke_usage INTEGER DEFAULT 0 util_molotov_usage INTEGER DEFAULT 0 util_he_usage INTEGER DEFAULT 0 util_decoy_usage INTEGER DEFAULT 0 damage_total INTEGER damage_received INTEGER damage_receive INTEGER damage_stats INTEGER assisted_kill INTEGER awp_kill INTEGER awp_kill_ct INTEGER awp_kill_t INTEGER benefit_kill INTEGER day TEXT defused_bomb INTEGER end_1v1 INTEGER end_1v2 INTEGER end_1v3 INTEGER end_1v4 INTEGER end_1v5 INTEGER explode_bomb INTEGER first_death INTEGER fd_ct INTEGER fd_t INTEGER first_kill INTEGER flash_enemy INTEGER flash_team INTEGER flash_team_time REAL flash_time REAL game_mode TEXT group_id INTEGER hold_total INTEGER id INTEGER is_highlight INTEGER is_most_1v2 INTEGER is_most_assist INTEGER is_most_awp INTEGER is_most_end INTEGER is_most_first_kill INTEGER is_most_headshot INTEGER is_most_jump INTEGER is_svp INTEGER is_tie INTEGER kill_1 INTEGER kill_2 INTEGER kill_3 INTEGER kill_4 INTEGER kill_5 INTEGER many_assists_cnt1 INTEGER many_assists_cnt2 INTEGER many_assists_cnt3 INTEGER many_assists_cnt4 INTEGER many_assists_cnt5 INTEGER map TEXT match_code TEXT match_mode TEXT match_team_id INTEGER match_time INTEGER per_headshot REAL perfect_kill INTEGER planted_bomb INTEGER revenge_kill INTEGER round_total INTEGER season TEXT team_kill INTEGER throw_harm INTEGER throw_harm_enemy INTEGER uid INTEGER year TEXT sts_raw TEXT level_info_raw TEXT TABLE: fact_match_players_ct Rows: 2,080 | Columns: 101 -------------------------------------------------------------------------------- CREATE TABLE fact_match_players_ct ( match_id TEXT, steam_id_64 TEXT, team_id INTEGER, kills INTEGER DEFAULT 0, deaths INTEGER DEFAULT 0, assists INTEGER DEFAULT 0, headshot_count INTEGER DEFAULT 0, kd_ratio REAL, adr REAL, rating REAL, rating2 REAL, rating3 REAL, rws REAL, mvp_count INTEGER DEFAULT 0, elo_change REAL, rank_score INTEGER, is_win BOOLEAN, kast REAL, entry_kills INTEGER, entry_deaths INTEGER, awp_kills INTEGER, clutch_1v1 INTEGER, clutch_1v2 INTEGER, clutch_1v3 INTEGER, clutch_1v4 INTEGER, clutch_1v5 INTEGER, flash_assists INTEGER, flash_duration REAL, jump_count INTEGER, damage_total INTEGER, damage_received INTEGER, damage_receive INTEGER, damage_stats INTEGER, assisted_kill INTEGER, awp_kill INTEGER, awp_kill_ct INTEGER, awp_kill_t INTEGER, benefit_kill INTEGER, day TEXT, defused_bomb INTEGER, end_1v1 INTEGER, end_1v2 INTEGER, end_1v3 INTEGER, end_1v4 INTEGER, end_1v5 INTEGER, explode_bomb INTEGER, first_death INTEGER, fd_ct INTEGER, fd_t INTEGER, first_kill INTEGER, flash_enemy INTEGER, flash_team INTEGER, flash_team_time REAL, flash_time REAL, game_mode TEXT, group_id INTEGER, hold_total INTEGER, id INTEGER, is_highlight INTEGER, is_most_1v2 INTEGER, is_most_assist INTEGER, is_most_awp INTEGER, is_most_end INTEGER, is_most_first_kill INTEGER, is_most_headshot INTEGER, is_most_jump INTEGER, is_svp INTEGER, is_tie INTEGER, kill_1 INTEGER, kill_2 INTEGER, kill_3 INTEGER, kill_4 INTEGER, kill_5 INTEGER, many_assists_cnt1 INTEGER, many_assists_cnt2 INTEGER, many_assists_cnt3 INTEGER, many_assists_cnt4 INTEGER, many_assists_cnt5 INTEGER, map TEXT, match_code TEXT, match_mode TEXT, match_team_id INTEGER, match_time INTEGER, per_headshot REAL, perfect_kill INTEGER, planted_bomb INTEGER, revenge_kill INTEGER, round_total INTEGER, season TEXT, team_kill INTEGER, throw_harm INTEGER, throw_harm_enemy INTEGER, uid INTEGER, year TEXT, sts_raw TEXT, level_info_raw TEXT, -- Utility Usage Stats (Parsed from round details) util_flash_usage INTEGER DEFAULT 0, util_smoke_usage INTEGER DEFAULT 0, util_molotov_usage INTEGER DEFAULT 0, util_he_usage INTEGER DEFAULT 0, util_decoy_usage INTEGER DEFAULT 0, PRIMARY KEY (match_id, steam_id_64), FOREIGN KEY (match_id) REFERENCES fact_matches(match_id) ON DELETE CASCADE ); COLUMNS: match_id TEXT [PK] steam_id_64 TEXT [PK] team_id INTEGER kills INTEGER DEFAULT 0 deaths INTEGER DEFAULT 0 assists INTEGER DEFAULT 0 headshot_count INTEGER DEFAULT 0 kd_ratio REAL adr REAL rating REAL rating2 REAL rating3 REAL rws REAL mvp_count INTEGER DEFAULT 0 elo_change REAL rank_score INTEGER is_win BOOLEAN kast REAL entry_kills INTEGER entry_deaths INTEGER awp_kills INTEGER clutch_1v1 INTEGER clutch_1v2 INTEGER clutch_1v3 INTEGER clutch_1v4 INTEGER clutch_1v5 INTEGER flash_assists INTEGER flash_duration REAL jump_count INTEGER damage_total INTEGER damage_received INTEGER damage_receive INTEGER damage_stats INTEGER assisted_kill INTEGER awp_kill INTEGER awp_kill_ct INTEGER awp_kill_t INTEGER benefit_kill INTEGER day TEXT defused_bomb INTEGER end_1v1 INTEGER end_1v2 INTEGER end_1v3 INTEGER end_1v4 INTEGER end_1v5 INTEGER explode_bomb INTEGER first_death INTEGER fd_ct INTEGER fd_t INTEGER first_kill INTEGER flash_enemy INTEGER flash_team INTEGER flash_team_time REAL flash_time REAL game_mode TEXT group_id INTEGER hold_total INTEGER id INTEGER is_highlight INTEGER is_most_1v2 INTEGER is_most_assist INTEGER is_most_awp INTEGER is_most_end INTEGER is_most_first_kill INTEGER is_most_headshot INTEGER is_most_jump INTEGER is_svp INTEGER is_tie INTEGER kill_1 INTEGER kill_2 INTEGER kill_3 INTEGER kill_4 INTEGER kill_5 INTEGER many_assists_cnt1 INTEGER many_assists_cnt2 INTEGER many_assists_cnt3 INTEGER many_assists_cnt4 INTEGER many_assists_cnt5 INTEGER map TEXT match_code TEXT match_mode TEXT match_team_id INTEGER match_time INTEGER per_headshot REAL perfect_kill INTEGER planted_bomb INTEGER revenge_kill INTEGER round_total INTEGER season TEXT team_kill INTEGER throw_harm INTEGER throw_harm_enemy INTEGER uid INTEGER year TEXT sts_raw TEXT level_info_raw TEXT util_flash_usage INTEGER DEFAULT 0 util_smoke_usage INTEGER DEFAULT 0 util_molotov_usage INTEGER DEFAULT 0 util_he_usage INTEGER DEFAULT 0 util_decoy_usage INTEGER DEFAULT 0 TABLE: fact_match_players_t Rows: 2,080 | Columns: 101 -------------------------------------------------------------------------------- CREATE TABLE fact_match_players_t ( match_id TEXT, steam_id_64 TEXT, team_id INTEGER, kills INTEGER DEFAULT 0, deaths INTEGER DEFAULT 0, assists INTEGER DEFAULT 0, headshot_count INTEGER DEFAULT 0, kd_ratio REAL, adr REAL, rating REAL, rating2 REAL, rating3 REAL, rws REAL, mvp_count INTEGER DEFAULT 0, elo_change REAL, rank_score INTEGER, is_win BOOLEAN, kast REAL, entry_kills INTEGER, entry_deaths INTEGER, awp_kills INTEGER, clutch_1v1 INTEGER, clutch_1v2 INTEGER, clutch_1v3 INTEGER, clutch_1v4 INTEGER, clutch_1v5 INTEGER, flash_assists INTEGER, flash_duration REAL, jump_count INTEGER, damage_total INTEGER, damage_received INTEGER, damage_receive INTEGER, damage_stats INTEGER, assisted_kill INTEGER, awp_kill INTEGER, awp_kill_ct INTEGER, awp_kill_t INTEGER, benefit_kill INTEGER, day TEXT, defused_bomb INTEGER, end_1v1 INTEGER, end_1v2 INTEGER, end_1v3 INTEGER, end_1v4 INTEGER, end_1v5 INTEGER, explode_bomb INTEGER, first_death INTEGER, fd_ct INTEGER, fd_t INTEGER, first_kill INTEGER, flash_enemy INTEGER, flash_team INTEGER, flash_team_time REAL, flash_time REAL, game_mode TEXT, group_id INTEGER, hold_total INTEGER, id INTEGER, is_highlight INTEGER, is_most_1v2 INTEGER, is_most_assist INTEGER, is_most_awp INTEGER, is_most_end INTEGER, is_most_first_kill INTEGER, is_most_headshot INTEGER, is_most_jump INTEGER, is_svp INTEGER, is_tie INTEGER, kill_1 INTEGER, kill_2 INTEGER, kill_3 INTEGER, kill_4 INTEGER, kill_5 INTEGER, many_assists_cnt1 INTEGER, many_assists_cnt2 INTEGER, many_assists_cnt3 INTEGER, many_assists_cnt4 INTEGER, many_assists_cnt5 INTEGER, map TEXT, match_code TEXT, match_mode TEXT, match_team_id INTEGER, match_time INTEGER, per_headshot REAL, perfect_kill INTEGER, planted_bomb INTEGER, revenge_kill INTEGER, round_total INTEGER, season TEXT, team_kill INTEGER, throw_harm INTEGER, throw_harm_enemy INTEGER, uid INTEGER, year TEXT, sts_raw TEXT, level_info_raw TEXT, -- Utility Usage Stats (Parsed from round details) util_flash_usage INTEGER DEFAULT 0, util_smoke_usage INTEGER DEFAULT 0, util_molotov_usage INTEGER DEFAULT 0, util_he_usage INTEGER DEFAULT 0, util_decoy_usage INTEGER DEFAULT 0, PRIMARY KEY (match_id, steam_id_64), FOREIGN KEY (match_id) REFERENCES fact_matches(match_id) ON DELETE CASCADE ); COLUMNS: match_id TEXT [PK] steam_id_64 TEXT [PK] team_id INTEGER kills INTEGER DEFAULT 0 deaths INTEGER DEFAULT 0 assists INTEGER DEFAULT 0 headshot_count INTEGER DEFAULT 0 kd_ratio REAL adr REAL rating REAL rating2 REAL rating3 REAL rws REAL mvp_count INTEGER DEFAULT 0 elo_change REAL rank_score INTEGER is_win BOOLEAN kast REAL entry_kills INTEGER entry_deaths INTEGER awp_kills INTEGER clutch_1v1 INTEGER clutch_1v2 INTEGER clutch_1v3 INTEGER clutch_1v4 INTEGER clutch_1v5 INTEGER flash_assists INTEGER flash_duration REAL jump_count INTEGER damage_total INTEGER damage_received INTEGER damage_receive INTEGER damage_stats INTEGER assisted_kill INTEGER awp_kill INTEGER awp_kill_ct INTEGER awp_kill_t INTEGER benefit_kill INTEGER day TEXT defused_bomb INTEGER end_1v1 INTEGER end_1v2 INTEGER end_1v3 INTEGER end_1v4 INTEGER end_1v5 INTEGER explode_bomb INTEGER first_death INTEGER fd_ct INTEGER fd_t INTEGER first_kill INTEGER flash_enemy INTEGER flash_team INTEGER flash_team_time REAL flash_time REAL game_mode TEXT group_id INTEGER hold_total INTEGER id INTEGER is_highlight INTEGER is_most_1v2 INTEGER is_most_assist INTEGER is_most_awp INTEGER is_most_end INTEGER is_most_first_kill INTEGER is_most_headshot INTEGER is_most_jump INTEGER is_svp INTEGER is_tie INTEGER kill_1 INTEGER kill_2 INTEGER kill_3 INTEGER kill_4 INTEGER kill_5 INTEGER many_assists_cnt1 INTEGER many_assists_cnt2 INTEGER many_assists_cnt3 INTEGER many_assists_cnt4 INTEGER many_assists_cnt5 INTEGER map TEXT match_code TEXT match_mode TEXT match_team_id INTEGER match_time INTEGER per_headshot REAL perfect_kill INTEGER planted_bomb INTEGER revenge_kill INTEGER round_total INTEGER season TEXT team_kill INTEGER throw_harm INTEGER throw_harm_enemy INTEGER uid INTEGER year TEXT sts_raw TEXT level_info_raw TEXT util_flash_usage INTEGER DEFAULT 0 util_smoke_usage INTEGER DEFAULT 0 util_molotov_usage INTEGER DEFAULT 0 util_he_usage INTEGER DEFAULT 0 util_decoy_usage INTEGER DEFAULT 0 TABLE: fact_match_teams Rows: 416 | Columns: 11 -------------------------------------------------------------------------------- CREATE TABLE fact_match_teams ( match_id TEXT, group_id INTEGER, group_all_score INTEGER, group_change_elo REAL, group_fh_role INTEGER, group_fh_score INTEGER, group_origin_elo REAL, group_sh_role INTEGER, group_sh_score INTEGER, group_tid INTEGER, group_uids TEXT, PRIMARY KEY (match_id, group_id), FOREIGN KEY (match_id) REFERENCES fact_matches(match_id) ON DELETE CASCADE ); COLUMNS: match_id TEXT [PK] group_id INTEGER [PK] group_all_score INTEGER group_change_elo REAL group_fh_role INTEGER group_fh_score INTEGER group_origin_elo REAL group_sh_role INTEGER group_sh_score INTEGER group_tid INTEGER group_uids TEXT TABLE: fact_matches Rows: 208 | Columns: 53 -------------------------------------------------------------------------------- CREATE TABLE fact_matches ( match_id TEXT PRIMARY KEY, match_code TEXT, map_name TEXT, start_time INTEGER, end_time INTEGER, duration INTEGER, winner_team INTEGER, -- 1 or 2 score_team1 INTEGER, score_team2 INTEGER, server_ip TEXT, server_port INTEGER, location TEXT, has_side_data_and_rating2 INTEGER, match_main_id INTEGER, demo_url TEXT, game_mode INTEGER, game_name TEXT, map_desc TEXT, location_full TEXT, match_mode INTEGER, match_status INTEGER, match_flag INTEGER, status INTEGER, waiver INTEGER, year INTEGER, season TEXT, round_total INTEGER, cs_type INTEGER, priority_show_type INTEGER, pug10m_show_type INTEGER, credit_match_status INTEGER, knife_winner INTEGER, knife_winner_role INTEGER, most_1v2_uid INTEGER, most_assist_uid INTEGER, most_awp_uid INTEGER, most_end_uid INTEGER, most_first_kill_uid INTEGER, most_headshot_uid INTEGER, most_jump_uid INTEGER, mvp_uid INTEGER, response_code INTEGER, response_message TEXT, response_status INTEGER, response_timestamp INTEGER, response_trace_id TEXT, response_success INTEGER, response_errcode INTEGER, treat_info_raw TEXT, round_list_raw TEXT, leetify_data_raw TEXT, data_source_type TEXT CHECK(data_source_type IN ('leetify', 'classic', 'unknown')), -- 'leetify' has economy data, 'classic' has detailed xyz processed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); COLUMNS: match_id TEXT [PK] match_code TEXT map_name TEXT start_time INTEGER end_time INTEGER duration INTEGER winner_team INTEGER score_team1 INTEGER score_team2 INTEGER server_ip TEXT server_port INTEGER location TEXT has_side_data_and_rating2 INTEGER match_main_id INTEGER demo_url TEXT game_mode INTEGER game_name TEXT map_desc TEXT location_full TEXT match_mode INTEGER match_status INTEGER match_flag INTEGER status INTEGER waiver INTEGER year INTEGER season TEXT round_total INTEGER cs_type INTEGER priority_show_type INTEGER pug10m_show_type INTEGER credit_match_status INTEGER knife_winner INTEGER knife_winner_role INTEGER most_1v2_uid INTEGER most_assist_uid INTEGER most_awp_uid INTEGER most_end_uid INTEGER most_first_kill_uid INTEGER most_headshot_uid INTEGER most_jump_uid INTEGER mvp_uid INTEGER response_code INTEGER response_message TEXT response_status INTEGER response_timestamp INTEGER response_trace_id TEXT response_success INTEGER response_errcode INTEGER treat_info_raw TEXT round_list_raw TEXT leetify_data_raw TEXT data_source_type TEXT processed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP TABLE: fact_round_events Rows: 33,560 | Columns: 29 -------------------------------------------------------------------------------- CREATE TABLE fact_round_events ( event_id TEXT PRIMARY KEY, -- UUID match_id TEXT, round_num INTEGER, event_type TEXT CHECK(event_type IN ('kill', 'bomb_plant', 'bomb_defuse', 'suicide', 'unknown')), event_time INTEGER, -- Seconds from round start -- Participants attacker_steam_id TEXT, victim_steam_id TEXT, assister_steam_id TEXT, flash_assist_steam_id TEXT, trade_killer_steam_id TEXT, -- Weapon & Context weapon TEXT, is_headshot BOOLEAN DEFAULT 0, is_wallbang BOOLEAN DEFAULT 0, is_blind BOOLEAN DEFAULT 0, is_through_smoke BOOLEAN DEFAULT 0, is_noscope BOOLEAN DEFAULT 0, -- Classic空间数据(xyz坐标) attacker_pos_x INTEGER, -- 仅classic attacker_pos_y INTEGER, -- 仅classic attacker_pos_z INTEGER, -- 仅classic victim_pos_x INTEGER, -- 仅classic victim_pos_y INTEGER, -- 仅classic victim_pos_z INTEGER, -- 仅classic -- Leetify评分影响 score_change_attacker REAL, -- 仅leetify score_change_victim REAL, -- 仅leetify twin REAL, -- 仅leetify (team win probability) c_twin REAL, -- 仅leetify twin_change REAL, -- 仅leetify c_twin_change REAL, -- 仅leetify -- 数据源标记 data_source_type TEXT CHECK(data_source_type IN ('leetify', 'classic', 'unknown')), FOREIGN KEY (match_id, round_num) REFERENCES fact_rounds(match_id, round_num) ON DELETE CASCADE ); COLUMNS: event_id TEXT [PK] match_id TEXT round_num INTEGER event_type TEXT event_time INTEGER attacker_steam_id TEXT victim_steam_id TEXT assister_steam_id TEXT flash_assist_steam_id TEXT trade_killer_steam_id TEXT weapon TEXT is_headshot BOOLEAN DEFAULT 0 is_wallbang BOOLEAN DEFAULT 0 is_blind BOOLEAN DEFAULT 0 is_through_smoke BOOLEAN DEFAULT 0 is_noscope BOOLEAN DEFAULT 0 attacker_pos_x INTEGER attacker_pos_y INTEGER attacker_pos_z INTEGER victim_pos_x INTEGER victim_pos_y INTEGER victim_pos_z INTEGER score_change_attacker REAL score_change_victim REAL twin REAL c_twin REAL twin_change REAL c_twin_change REAL data_source_type TEXT TABLE: fact_round_player_economy Rows: 5,930 | Columns: 13 -------------------------------------------------------------------------------- CREATE TABLE fact_round_player_economy ( match_id TEXT, round_num INTEGER, steam_id_64 TEXT, side TEXT CHECK(side IN ('CT', 'T')), -- Leetify经济数据(仅leetify) start_money INTEGER, equipment_value INTEGER, main_weapon TEXT, has_helmet BOOLEAN, has_defuser BOOLEAN, has_zeus BOOLEAN, round_performance_score REAL, -- Classic装备快照(仅classic, JSON存储) equipment_snapshot_json TEXT, -- Classic的equiped字段序列化 -- 数据源标记 data_source_type TEXT CHECK(data_source_type IN ('leetify', 'classic', 'unknown')), PRIMARY KEY (match_id, round_num, steam_id_64), FOREIGN KEY (match_id, round_num) REFERENCES fact_rounds(match_id, round_num) ON DELETE CASCADE ); COLUMNS: match_id TEXT [PK] round_num INTEGER [PK] steam_id_64 TEXT [PK] side TEXT start_money INTEGER equipment_value INTEGER main_weapon TEXT has_helmet BOOLEAN has_defuser BOOLEAN has_zeus BOOLEAN round_performance_score REAL equipment_snapshot_json TEXT data_source_type TEXT TABLE: fact_rounds Rows: 4,315 | Columns: 16 -------------------------------------------------------------------------------- CREATE TABLE fact_rounds ( match_id TEXT, round_num INTEGER, -- 公共字段(两种数据源均有) winner_side TEXT CHECK(winner_side IN ('CT', 'T', 'None')), win_reason INTEGER, -- Raw integer from source win_reason_desc TEXT, -- Mapped description (e.g. 'TargetBombed') duration REAL, ct_score INTEGER, t_score INTEGER, -- Leetify专属字段 ct_money_start INTEGER, -- 仅leetify t_money_start INTEGER, -- 仅leetify begin_ts TEXT, -- 仅leetify end_ts TEXT, -- 仅leetify -- Classic专属字段 end_time_stamp TEXT, -- 仅classic final_round_time INTEGER, -- 仅classic pasttime INTEGER, -- 仅classic -- 数据源标记(继承自fact_matches) data_source_type TEXT CHECK(data_source_type IN ('leetify', 'classic', 'unknown')), PRIMARY KEY (match_id, round_num), FOREIGN KEY (match_id) REFERENCES fact_matches(match_id) ON DELETE CASCADE ); COLUMNS: match_id TEXT [PK] round_num INTEGER [PK] winner_side TEXT win_reason INTEGER win_reason_desc TEXT duration REAL ct_score INTEGER t_score INTEGER ct_money_start INTEGER t_money_start INTEGER begin_ts TEXT end_ts TEXT end_time_stamp TEXT final_round_time INTEGER pasttime INTEGER data_source_type TEXT