charles-tan

BEGIN APPLICATION rage_quitters

CREATE VIRTUAL STREAM virtual.public.games_finished AS 
SELECT * FROM player_game_actions WITH ('timestamp' = 'event_ts') WHERE "action" = 'QUIT' OR "action" = 'COMPLETE';

CREATE VIRTUAL STREAM virtual.public.num_recent_games_quit AS 
SELECT 
  player_id, 
  "action" AS latest_action, 
  event_ts, 

charles-tan

CREATE STREAM player_game_actions (
  event_ts TIMESTAMP, 
  game_lobby_uuid VARCHAR,
  player_id VARCHAR, 
  "action" VARCHAR
) WITH (
  'topic' = 'player_game_actions', 'value.format' = 'json'
);

charles-tan

{
    "event_ts": "2024-01-17 12:12:58.742",
    "game_lobby_id": "22D9586J",
    "player_id": "FruitDealer",
    "action": "QUIT"
}

charles-tan

SELECT 
  *,
  (CAST(games_won AS DOUBLE) / games_played) AS win_loss_ratio 
FROM player_wins 
ORDER BY win_loss_ratio DESC 
LIMIT 10;

charles-tan

SELECT * FROM player_wins ORDER BY games_won DESC LIMIT 10;

charles-tan

CREATE MATERIALIZED VIEW player_wins AS 
SELECT 
  player_id, 
  COUNT(*) AS games_played, 
  COUNT(CASE WHEN win = true THEN 1 END) AS games_won 
FROM game_results 
GROUP BY player_id;

charles-tan

CREATE STREAM game_results (
  event_ts TIMESTAMP, 
  player_id VARCHAR, 
  win BOOLEAN
) WITH (
  'topic' = 'game_results', 'value.format' = 'json'
);

charles-tan

{
    "event_ts": "2024-02-17 11:53:39.321",
    "player_id": "FruitDealer",
    "win": true
}

charles-tan

CREATE STORE redpanda_store WITH (
  'type' = KAFKA,
  'access_region' = "AWS us-east-1", 
  'uris' = 'demo-url.fmc.prd.cloud.redpanda.com:9092', 
  'kafka.sasl.hash_function' = SHA512, 
  'kafka.sasl.username' = 'username', 
  'kafka.sasl.password' = 'password'
);

charles-tan

topic=source, partition=0, startingOffset=3, stoppingOffset=Optional.empty, topicPartition=source-0
DONE