Chaos Tomb: Visualizing Gameplay with D3 and SQL

select level,
       sum(leveltime) as totaltime,
       count(distinct playerid) as playercount,
       count(case
             when statustype='drown' then 1
             else null
             end) as status_drown,
       count(case
             when statustype='monster' then 1
             else null
             end) as status_monster,
       count(case
             when statustype='door' then 1
             else null
             end) as status_door
from ct_status
     natural left join ct_game
group by level;

select count(1),
       date_trunc('hour', statustime) as hour
from ct_player
     natural right join ct_game
     natural right join ct_status
group by hour
order by hour;

select sum(leveltime)
from ct_game
     natural right join ct_status
group by playerid;

select count(distinct level)
from ct_game
     natural right join ct_status
group by playerid;

/*
 * 'chests' is a bitfield, so we want to count the number of ones.
 * Get the maximum number of chests that each player ever opened.
 */
select max(length(replace(cast(chests as text), '0', '')))
from ct_game
     natural right join ct_status
group by playerid;

/*
 * 'weapons' is a bitfield, so we want to count the number of ones.
 * Get the maximum number of weapons that each player ever had.
 */
select max(length(replace(cast(weapons as text), '0', '')))
from ct_game
     natural right join ct_status
group by playerid;

select max(hearts)
from ct_game
     natural right join ct_status
group by playerid;

select bool_or(djump)
from ct_game
     natural right join ct_status
group by playerid;

select count(case
             when statustype='monster' or statustype='drown' then 1
             else null
             end) as deathcount
from ct_game
     natural right join ct_status
group by playerid;

select bool_or(statustype = 'end')
from ct_game
     natural right join ct_status
group by playerid;

select family(clientaddress) as family,
       count(1)
from (select distinct playerid
      from ct_game
      natural right join ct_status) as t1
      natural right join ct_player
group by family;

/* Unique players */
create table ct_player (
    playerid serial not null,
    /* Random string for identifying the player */
    playerkey text,
    /* The HTTP referrer for when the player first arrived */
    referrer text,
    /* The player's user agent string */
    useragent text,
    /* The first IP address the player used */
    clientaddress inet not null,
    constraint ct_player_pkey
        primary key (playerid)
);

create unique index ct_player_key on ct_player (playerkey);

/* List of games */
create table ct_game (
    gameid serial not null,
    /* Random string for identifying the game */
    gamekey text,
    playerid integer not null,
    /* Time when the game was started */
    starttime timestamp with time zone not null default now(),
    constraint ct_game_pkey
        primary key(gameid),
    constraint ct_game_playerid
        foreign key (playerid) references ct_player
        on delete cascade
        on update cascade
);

create unique index ct_game_key on ct_game (gamekey);

/* Reasons why a status is being recorded */
create type ct_statustype as enum (
    'drown',   /* Player drowned in water or lava */
    'monster', /* Monster killed the player */
    'door',    /* Player went through a door to another level */
    'end'      /* Player left the cave, ending the game */
);

/* Snapshot of a game when a level ended */
create table ct_status (
    gameid integer not null,
    statustime timestamp with time zone not null default now(),
    /* Reason why the level ended (e.g. died or exited through door) */
    statustype ct_statustype not null,
    /* Name of the level */
    level text not null,
    /* Number of level transitions so far this game */
    levelindex int not null,
    /* Amount of time spent in this level */
    leveltime interval not null,
    /* Bitmask for which weapons the player has */
    weapons bit(6) not null,
    /* Whether the player has the double-jump powerup */
    djump boolean not null,
    /* Bitmask for which chests have been opened */
    chests bit(13) not null,
    /* Whether the player has cheated during this game */
    cheat boolean not null,
    /* Number of times the player has saved the game */
    savecount int not null,
    /* Number of times the player has loaded the game */
    loadcount int not null,
    /* Current player health */
    health int not null,
    /* Number of hearts acquired */
    hearts int not null,
    constraint ct_status_gameid
        foreign key (gameid) references ct_game
        on delete cascade
        on update cascade
);