A live sports scoreboard updates the moment a goal is scored, a foul is called, or a free throw drops through the net. Building one with streaming SQL means you express those updates in plain SQL, and the database keeps every view current automatically. This tutorial walks you through a working implementation in RisingWave, a PostgreSQL-compatible streaming database, covering game event ingestion, running score computation, player stat tracking, and a live standings table.
What You Will Build
By the end of this tutorial, you will have:
- A live game scoreboard that reflects every point as it is scored
- A player stats view tracking points, shot types, and fouls per game
- A foul tracker that flags players approaching disqualification
- A standings table that updates automatically when a game ends
Every query in this article was tested against RisingWave 2.8.0 running locally. The SQL is standard and PostgreSQL-compatible: no custom DSL, no Java, no YAML configuration files.
Why Streaming SQL for Sports Data?
Traditional sports scoreboards are driven by one of two approaches: polling (query a database every few seconds) or pub/sub push via WebSockets with manual state management on the server. Both work, but both require you to maintain state outside the database.
Streaming SQL turns that around. You define what the scoreboard should show as a materialized view, and the database maintains it incrementally as new events arrive. A goal event arrives, and within milliseconds the score view reflects it. No polling loop. No cache invalidation. No separate aggregation service.
RisingWave specifically shines here because:
- It stores materialized view state persistently (no re-computation on restart)
- It supports materialized view chaining so downstream views like standings automatically update when upstream score views change
- It exposes results over a standard PostgreSQL wire protocol, so your existing dashboard tools work without modification
Data Model
The scoreboard needs three tables: teams, games, and the event stream itself.
CREATE TABLE sports_teams (
team_id INT PRIMARY KEY,
team_name VARCHAR,
city VARCHAR,
sport VARCHAR
);
CREATE TABLE sports_games (
game_id INT PRIMARY KEY,
home_team INT,
away_team INT,
game_date DATE,
venue VARCHAR,
status VARCHAR -- 'SCHEDULED', 'LIVE', 'FINAL'
);
CREATE TABLE sports_events (
event_id BIGINT,
game_id INT,
event_type VARCHAR, -- 'TWO_POINTER', 'THREE_POINTER', 'FREE_THROW', 'FOUL'
team_id INT,
player_name VARCHAR,
points INT, -- point value (0 for fouls)
event_ts TIMESTAMPTZ
);
The sports_events table is the heart of the system. Every meaningful thing that happens in a game lands here as a row. This is the event sourcing pattern: raw facts arrive in append-only form, and derived views like scores and standings are computed from those facts. There is no mutable "current score" field to keep in sync.
The status column on sports_games controls visibility in the standings view. Only games marked FINAL count toward team records.
Loading Sample Data
Add four teams and two live games, then insert the play-by-play events for the first quarter:
INSERT INTO sports_teams VALUES
(1, 'River Hawks', 'Chicago', 'Basketball'),
(2, 'Bay Wolves', 'San Francisco', 'Basketball'),
(3, 'Desert Suns', 'Phoenix', 'Basketball'),
(4, 'Lake Sharks', 'Detroit', 'Basketball');
INSERT INTO sports_games VALUES
(101, 1, 2, '2026-04-01', 'United Center', 'LIVE'),
(102, 3, 4, '2026-04-01', 'Footprint Center', 'LIVE');
Now insert the play-by-play events. In a production system these would flow from Kafka or a CDC source, but INSERT works identically for local development:
INSERT INTO sports_events VALUES
(1, 101, 'TWO_POINTER', 1, 'Marcus Johnson', 2, '2026-04-01 19:02:10+00'),
(2, 101, 'THREE_POINTER', 2, 'Derek Smith', 3, '2026-04-01 19:03:45+00'),
(3, 101, 'FOUL', 1, 'Carlos Rivera', 0, '2026-04-01 19:04:30+00'),
(4, 101, 'FREE_THROW', 2, 'Derek Smith', 1, '2026-04-01 19:04:50+00'),
(5, 101, 'TWO_POINTER', 1, 'James Lee', 2, '2026-04-01 19:06:20+00'),
(6, 101, 'THREE_POINTER', 1, 'Marcus Johnson', 3, '2026-04-01 19:08:00+00'),
(7, 101, 'FOUL', 2, 'Ray Patel', 0, '2026-04-01 19:09:10+00'),
(8, 101, 'FREE_THROW', 1, 'James Lee', 1, '2026-04-01 19:09:25+00'),
(9, 101, 'FREE_THROW', 1, 'James Lee', 1, '2026-04-01 19:09:40+00'),
(10, 101, 'TWO_POINTER', 2, 'Andre Wilson', 2, '2026-04-01 19:11:00+00'),
(11, 101, 'TWO_POINTER', 2, 'Ray Patel', 2, '2026-04-01 19:12:30+00'),
(12, 101, 'THREE_POINTER', 2, 'Derek Smith', 3, '2026-04-01 19:14:00+00'),
(13, 101, 'FOUL', 1, 'Marcus Johnson', 0, '2026-04-01 19:15:10+00'),
(14, 101, 'FREE_THROW', 2, 'Derek Smith', 1, '2026-04-01 19:15:20+00'),
(15, 101, 'TWO_POINTER', 1, 'Carlos Rivera', 2, '2026-04-01 19:17:00+00');
That covers the first quarter of game 101. Game 102 has its own events following the same pattern.
The Live Scoreboard: sports_game_scores
The scoreboard view sums points for each team per game, separating home from away using a CASE expression that looks up which team is home in the sports_games table:
CREATE MATERIALIZED VIEW sports_game_scores AS
SELECT
e.game_id,
g.venue,
g.status,
ht.team_name AS home_team,
at.team_name AS away_team,
SUM(CASE WHEN e.team_id = g.home_team THEN e.points ELSE 0 END) AS home_score,
SUM(CASE WHEN e.team_id = g.away_team THEN e.points ELSE 0 END) AS away_score,
COUNT(*) AS total_events,
MAX(e.event_ts) AS last_event_ts
FROM sports_events e
JOIN sports_games g ON e.game_id = g.game_id
JOIN sports_teams ht ON g.home_team = ht.team_id
JOIN sports_teams at ON g.away_team = at.team_id
GROUP BY e.game_id, g.venue, g.status, g.home_team, g.away_team,
ht.team_name, at.team_name;
Query it immediately:
SELECT
game_id,
venue,
home_team,
home_score,
away_score,
away_team,
status,
total_events
FROM sports_game_scores
ORDER BY game_id;
game_id | venue | home_team | home_score | away_score | away_team | status | total_events
---------+------------------+-------------+------------+------------+-------------+--------+--------------
101 | United Center | River Hawks | 11 | 12 | Bay Wolves | LIVE | 15
102 | Footprint Center | Desert Suns | 9 | 8 | Lake Sharks | LIVE | 11
Game 101: Bay Wolves lead 12-11. Game 102: Desert Suns lead 9-8. Both games are tight. The view will reflect any subsequent INSERT into sports_events within milliseconds, with no application code involved.
Player Stats: sports_player_stats
The player stats view breaks down each player's contribution using conditional aggregation. The FILTER clause, standard in PostgreSQL-compatible SQL, counts only rows matching a specific condition:
CREATE MATERIALIZED VIEW sports_player_stats AS
SELECT
e.player_name,
t.team_name,
e.game_id,
SUM(e.points) AS total_points,
COUNT(*) FILTER (WHERE e.event_type = 'TWO_POINTER') AS two_pointers,
COUNT(*) FILTER (WHERE e.event_type = 'THREE_POINTER') AS three_pointers,
COUNT(*) FILTER (WHERE e.event_type = 'FREE_THROW') AS free_throws,
COUNT(*) FILTER (WHERE e.event_type = 'FOUL') AS fouls,
MAX(e.event_ts) AS last_event_ts
FROM sports_events e
JOIN sports_teams t ON e.team_id = t.team_id
GROUP BY e.player_name, t.team_name, e.game_id;
Check the leaderboard for game 101:
SELECT
player_name,
team_name,
total_points,
two_pointers,
three_pointers,
free_throws,
fouls
FROM sports_player_stats
WHERE game_id = 101
ORDER BY total_points DESC;
player_name | team_name | total_points | two_pointers | three_pointers | free_throws | fouls
----------------+-------------+--------------+--------------+----------------+-------------+-------
Derek Smith | Bay Wolves | 8 | 0 | 2 | 2 | 0
Marcus Johnson | River Hawks | 5 | 1 | 1 | 0 | 1
James Lee | River Hawks | 4 | 1 | 0 | 2 | 0
Ray Patel | Bay Wolves | 2 | 1 | 0 | 0 | 1
Andre Wilson | Bay Wolves | 2 | 1 | 0 | 0 | 0
Carlos Rivera | River Hawks | 2 | 1 | 0 | 0 | 1
Derek Smith is carrying the Bay Wolves with 8 points on two three-pointers and two free throws. Marcus Johnson leads the River Hawks with 5 points but already has a foul.
Tracking Fouls: sports_foul_summary
Foul trouble is one of the most important real-time signals in basketball. A player who picks up 4 fouls in the first half is in danger of sitting out the fourth quarter. The sports_foul_summary view flags players by status:
CREATE MATERIALIZED VIEW sports_foul_summary AS
SELECT
e.game_id,
t.team_name,
e.player_name,
COUNT(*) FILTER (WHERE e.event_type = 'FOUL') AS foul_count,
CASE
WHEN COUNT(*) FILTER (WHERE e.event_type = 'FOUL') >= 5 THEN 'FOULED_OUT'
WHEN COUNT(*) FILTER (WHERE e.event_type = 'FOUL') = 4 THEN 'FOUL_TROUBLE'
WHEN COUNT(*) FILTER (WHERE e.event_type = 'FOUL') = 3 THEN 'WATCH'
ELSE 'OK'
END AS foul_status
FROM sports_events e
JOIN sports_teams t ON e.team_id = t.team_id
WHERE e.event_type IN ('FOUL', 'TWO_POINTER', 'THREE_POINTER', 'FREE_THROW')
GROUP BY e.game_id, t.team_name, e.player_name
HAVING COUNT(*) FILTER (WHERE e.event_type = 'FOUL') > 0;
SELECT game_id, team_name, player_name, foul_count, foul_status
FROM sports_foul_summary
ORDER BY game_id, foul_count DESC;
game_id | team_name | player_name | foul_count | foul_status
---------+-------------+----------------+------------+-------------
101 | River Hawks | Carlos Rivera | 1 | OK
101 | River Hawks | Marcus Johnson | 1 | OK
101 | Bay Wolves | Ray Patel | 1 | OK
102 | Lake Sharks | Chris Banks | 1 | OK
102 | Desert Suns | Tony Green | 1 | OK
Everyone is at one foul. That changes the moment a second or third foul is called.
The Standings Table: sports_standings
Standings track wins and losses across completed games. The view only counts games in FINAL status, which means live games in progress do not affect team records. The logic uses a common table expression (CTE) to compute per-game outcomes, then aggregates them by team:
CREATE MATERIALIZED VIEW sports_standings AS
WITH game_totals AS (
SELECT
e.game_id,
e.team_id,
SUM(e.points) AS team_score
FROM sports_events e
GROUP BY e.game_id, e.team_id
),
game_pairs AS (
SELECT
h.game_id,
h.team_id AS home_team_id,
a.team_id AS away_team_id,
h.team_score AS home_score,
a.team_score AS away_score,
g.status
FROM game_totals h
JOIN game_totals a ON h.game_id = a.game_id
AND h.team_id != a.team_id
JOIN sports_games g ON h.game_id = g.game_id
WHERE h.team_id = g.home_team
AND a.team_id = g.away_team
AND g.status = 'FINAL'
),
team_records AS (
SELECT
home_team_id AS team_id,
CASE WHEN home_score > away_score THEN 1 ELSE 0 END AS win,
CASE WHEN home_score < away_score THEN 1 ELSE 0 END AS loss,
home_score AS points_for,
away_score AS points_against
FROM game_pairs
UNION ALL
SELECT
away_team_id AS team_id,
CASE WHEN away_score > home_score THEN 1 ELSE 0 END AS win,
CASE WHEN away_score < home_score THEN 1 ELSE 0 END AS loss,
away_score AS points_for,
home_score AS points_against
FROM game_pairs
)
SELECT
t.team_name,
t.city,
SUM(r.win) AS wins,
SUM(r.loss) AS losses,
SUM(r.points_for) AS total_points_scored,
SUM(r.points_against) AS total_points_allowed,
SUM(r.points_for) - SUM(r.points_against) AS point_differential
FROM team_records r
JOIN sports_teams t ON r.team_id = t.team_id
GROUP BY t.team_name, t.city;
While games are LIVE, standings are empty, which is correct:
SELECT team_name, city, wins, losses, total_points_scored, point_differential
FROM sports_standings
ORDER BY wins DESC, point_differential DESC;
team_name | city | wins | losses | total_points_scored | point_differential
-----------+------+------+--------+---------------------+--------------------
(0 rows)
Now finish the games:
UPDATE sports_games SET status = 'FINAL' WHERE game_id IN (101, 102);
Query standings again:
SELECT team_name, city, wins, losses, total_points_scored, total_points_allowed, point_differential
FROM sports_standings
ORDER BY wins DESC, point_differential DESC;
team_name | city | wins | losses | total_points_scored | total_points_allowed | point_differential
-------------+---------------+------+--------+---------------------+----------------------+--------------------
Bay Wolves | San Francisco | 1 | 0 | 12 | 11 | 1
Desert Suns | Phoenix | 1 | 0 | 9 | 8 | 1
River Hawks | Chicago | 0 | 1 | 11 | 12 | -1
Lake Sharks | Detroit | 0 | 1 | 8 | 9 | -1
Bay Wolves and Desert Suns each won their first game. River Hawks and Lake Sharks are 0-1. The standings table updated the instant you ran the UPDATE, with no additional code.
Watching Updates Happen in Real Time
The most satisfying part of streaming SQL is watching live updates cascade. Set the games back to LIVE and simulate the next three plays arriving mid-game:
UPDATE sports_games SET status = 'LIVE' WHERE game_id IN (101, 102);
INSERT INTO sports_events VALUES
(27, 101, 'THREE_POINTER', 1, 'Marcus Johnson', 3, '2026-04-01 19:19:00+00'),
(28, 101, 'FOUL', 1, 'Marcus Johnson', 0, '2026-04-01 19:20:10+00'),
(29, 101, 'FREE_THROW', 2, 'Derek Smith', 1, '2026-04-01 19:20:30+00');
Query the scoreboard:
SELECT game_id, home_team, home_score, away_score, away_team
FROM sports_game_scores
WHERE game_id = 101;
game_id | home_team | home_score | away_score | away_team
---------+-------------+------------+------------+------------
101 | River Hawks | 14 | 13 | Bay Wolves
River Hawks retook the lead 14-13. Marcus Johnson's three-pointer swung the game. Check his updated profile:
SELECT player_name, team_name, total_points, three_pointers, fouls
FROM sports_player_stats
WHERE player_name = 'Marcus Johnson'
AND game_id = 101;
player_name | team_name | total_points | three_pointers | fouls
----------------+-------------+--------------+----------------+-------
Marcus Johnson | River Hawks | 8 | 2 | 2
And his foul status:
SELECT player_name, foul_count, foul_status
FROM sports_foul_summary
WHERE player_name = 'Marcus Johnson'
AND game_id = 101;
player_name | foul_count | foul_status
----------------+------------+-------------
Marcus Johnson | 2 | OK
Marcus Johnson jumped from 5 to 8 points, and his foul count is now 2. The coach will want to keep an eye on him. Three more fouls and he is out of the game. This is exactly the kind of real-time signal that matters in a live context.
Three inserts, and every downstream view updated automatically: the scoreboard, the player stats, and the foul tracker. No polling. No cache bust. No application-layer aggregation.
Architecture Diagram
Here is how all the pieces connect:
graph LR
A[Game Event Source] -->|INSERT / Kafka / CDC| B[sports_events]
C[sports_teams] --> D[sports_game_scores MV]
E[sports_games] --> D
B --> D
B --> F[sports_player_stats MV]
C --> F
B --> G[sports_foul_summary MV]
C --> G
B --> H[sports_standings MV]
C --> H
E --> H
D --> I[Live Scoreboard UI]
F --> J[Player Stats Panel]
G --> K[Foul Alert Widget]
H --> L[League Table]
Each materialized view reads from sports_events (and the reference tables) and updates independently. Adding a new view, for example a shot-chart breakdown by court zone, requires no changes to existing views or the ingestion layer.
Connecting to Real Event Sources
The tutorial uses direct INSERT for simplicity, but a production sports data pipeline would ingest from an API or message broker. RisingWave supports this natively.
For a Kafka-backed event stream (common when sports tracking software publishes to a message bus):
CREATE TABLE sports_events (
event_id BIGINT,
game_id INT,
event_type VARCHAR,
team_id INT,
player_name VARCHAR,
points INT,
event_ts TIMESTAMPTZ
) WITH (
connector = 'kafka',
topic = 'sports-game-events',
properties.bootstrap.server = 'kafka:9092',
scan.startup.mode = 'earliest'
) FORMAT PLAIN ENCODE JSON;
That single CREATE TABLE statement replaces the Kafka consumer application, deserialization code, and storage layer. The materialized views above continue working without modification. See the RisingWave Kafka source connector documentation for full configuration options.
For a PostgreSQL or MySQL backend that already tracks game events, you can connect via CDC so RisingWave streams every new row automatically:
CREATE TABLE sports_events (
event_id BIGINT PRIMARY KEY,
game_id INT,
event_type VARCHAR,
team_id INT,
player_name VARCHAR,
points INT,
event_ts TIMESTAMPTZ
) WITH (
connector = 'postgres-cdc',
hostname = 'pg-host',
port = '5432',
username = 'replicator',
password = 'secret',
database.name = 'sportsdb',
schema.name = 'public',
table.name = 'game_events'
);
RisingWave reads the PostgreSQL replication slot and makes every new event available to materialized views within milliseconds. See the PostgreSQL CDC documentation for the full setup guide.
Performance Characteristics
A few details worth knowing before going to production:
Low latency, bounded state. RisingWave updates materialized views incrementally. A new event for game 101 only re-evaluates the rows affected by that game in each view, not the entire dataset. With hundreds of concurrent games, state is proportional to the number of active game-player combinations, not the total event count.
MV-on-MV propagation. If you built a view on top of sports_game_scores (say, to filter only games within 5 points for a "close games" widget), that view also updates automatically when a new event arrives. RisingWave propagates changes through the materialized view graph, described in the incremental materialized views documentation.
Serving via PostgreSQL protocol. Any tool that can connect to PostgreSQL can query RisingWave: psql, pgAdmin, Grafana, Retool, or a Python application using psycopg2. This means your scoreboard frontend can be built with a standard database driver, with no custom WebSocket server in between.
FAQ
Can this handle multiple sports simultaneously?
Yes. The sport column on sports_teams and the event_type field on sports_events allow you to extend the model for soccer (goals, assists, yellow cards), baseball (hits, runs, errors), or American football (touchdowns, field goals, penalties). You would add sport-specific event types and potentially separate materialized views per sport. RisingWave runs all views concurrently with no interaction between them.
How does RisingWave compare to Apache Flink for building a scoreboard?
Both can handle the streaming aggregations in this tutorial, but the development experience differs significantly. Flink requires writing Java or Scala, managing a separate state backend (RocksDB by default), and operating a standalone cluster. RisingWave is a database: you write SQL, connect with any PostgreSQL client, and skip the JVM entirely. For a team comfortable with SQL but not stream processing frameworks, RisingWave is dramatically faster to get running. See Apache Flink vs RisingWave for a detailed side-by-side.
What happens if a scoring event arrives out of order?
RisingWave processes events as they arrive. If a delayed event arrives with an older timestamp, it is aggregated into the current totals just like any other row. The MAX(event_ts) column in sports_game_scores tracks the latest event timestamp, but the score itself is the sum of all points regardless of arrival order. For use cases requiring strict time-ordering semantics (like session windows or time-based leaderboards), RisingWave supports watermarks and event-time windowing using the time window functions in the documentation.
Can I push score updates to a frontend in real time?
Yes, through multiple approaches. The simplest is polling: your frontend queries sports_game_scores every second over a standard HTTP API backed by PostgreSQL. For true push delivery, connect RisingWave to a Kafka sink, then have a WebSocket server fan out Kafka messages to browser clients. This architecture decouples the streaming computation (RisingWave) from the delivery mechanism (WebSocket server) cleanly.
Conclusion
Building a live sports scoreboard with streaming SQL is more accessible than it sounds. You define a data model with three tables, write four materialized views, and RisingWave handles everything else: incremental updates, cascading propagation, and query serving over a standard PostgreSQL interface.
What you built in this tutorial:
- sports_game_scores - A continuously updated scoreboard showing home/away scores for all live games
- sports_player_stats - Per-player point totals, shot breakdowns, and foul counts, updated with each new event
- sports_foul_summary - A foul tracker that flags players approaching disqualification in real time
- sports_standings - A standings table that updates automatically when a game moves to
FINALstatus
The same pattern scales from a weekend hackathon project to a production deployment handling thousands of simultaneous games. Swap the INSERT statements for a Kafka source or CDC connection, and the rest of your SQL remains unchanged.
Want to try this yourself? Get started with RisingWave for free. The local playground runs on any laptop in under a minute.
Join the RisingWave Slack community to ask questions, share what you build, and connect with other developers working on real-time data.
