Querying 30 Years of the UFC with SQL

Turning a raw scrape into a clean relational database, then asking it hard questions

This notebook is a portfolio case study by AbleVLabs. It takes a real, messy scrape of every UFC event, fight, and fighter from ufcstats.com and does two things a data analyst is paid to do: model the data into a clean relational schema, and then answer real questions with SQL, from simple joins up to window functions.

Every query below runs against the same SQLite database that powers the live query console on the dashboard, so anything here can be re-run and explored in the browser.

1. The schema

The raw scrape arrives as flat, denormalized CSVs. It was normalized into four related tables with proper keys, so that fighters, events, and fights are each stored once and linked by id. This is the design an analyst reasons about before writing a single query.

import sqlite3, pandas as pd
import matplotlib.pyplot as plt
plt.rcParams.update({'figure.facecolor':'#0d0d12','axes.facecolor':'#12121a',
  'savefig.facecolor':'#0d0d12','text.color':'#e8e8ef','axes.labelcolor':'#e8e8ef',
  'xtick.color':'#a9a9c0','ytick.color':'#a9a9c0','axes.edgecolor':'#2a2a3a',
  'grid.color':'#20202c','font.size':11,'axes.titlecolor':'#e8e8ef','figure.dpi':110})
CY,PU,GR,AM,RED='#22d3ee','#a855f7','#34d399','#f59e0b','#ef4444'
pd.set_option('display.max_colwidth',40)

con=sqlite3.connect('ufc.sqlite')
tables=pd.read_sql("SELECT name FROM sqlite_master WHERE type='table'", con)['name'].tolist()
for t in tables:
    n=pd.read_sql(f'SELECT COUNT(*) c FROM {t}', con)['c'][0]
    cols=pd.read_sql(f'PRAGMA table_info({t})', con)['name'].tolist()
    print(f'{t:12s} {n:6d} rows   ({", ".join(cols)})')

events          789 rows   (event_id, name, date, city, country)
fighters       4623 rows   (fighter_id, name, height_in, reach_in, weight_lbs, stance, dob)
bouts          8885 rows   (bout_id, event_id, fighter1_id, fighter2_id, winner_id, result_type, weight_class, title_bout, method, method_group, round, time, end_secs, referee, duration_secs)
fight_stats   17726 rows   (bout_id, fighter_id, kd, sig_str_landed, sig_str_att, total_str_landed, td_landed, td_att, sub_att, reversals, ctrl_secs)

The relationships in one line: bouts is the hub. Each bout points to an event and to two fighters, names a winner, and has matching rows in fight_stats. That star shape is what makes the joins below natural.

2. Who has the most UFC wins?

Technique: JOIN + GROUP BY + COUNT. Join every bout to its winner, then count wins per fighter. The simplest bread-and-butter query, and the foundation everything else builds on.

sql = """\
SELECT f.name, COUNT(*) AS wins
FROM bouts b
JOIN fighters f ON f.fighter_id = b.winner_id
GROUP BY f.fighter_id
ORDER BY wins DESC
LIMIT 10;
"""
df = pd.read_sql(sql, con)
df

	name	wins
0	Jim Miller	28
1	Neil Magny	25
2	Charles Oliveira	25
3	Max Holloway	24
4	Donald Cerrone	23
5	Andrei Arlovski	23
6	Jon Jones	22
7	Dustin Poirier	22
8	Demian Maia	22
9	Rafael Dos Anjos	21

ax=df.iloc[::-1].plot.barh(x='name',y='wins',color=CY,legend=False,figsize=(8,4))
ax.set_title("Who has the most UFC wins?"); ax.set_xlabel('wins'); plt.tight_layout(); plt.show()

3. Which fighters finish, instead of coasting to a decision?

Technique: CTE + CASE + ratio. A CTE counts each fighter's wins and how many were finishes (KO or submission), then the outer query turns that into a finish percentage. CASE inside SUM is the classic way to count a condition.

sql = """\
WITH w AS (
  SELECT winner_id AS fid,
         COUNT(*) AS wins,
         SUM(CASE WHEN method_group IN ('KO/TKO','Submission') THEN 1 ELSE 0 END) AS finishes
  FROM bouts
  WHERE winner_id IS NOT NULL
  GROUP BY winner_id
)
SELECT f.name, w.wins, w.finishes,
       ROUND(100.0 * w.finishes / w.wins, 1) AS finish_pct
FROM w
JOIN fighters f ON f.fighter_id = w.fid
WHERE w.wins >= 15
ORDER BY finish_pct DESC, wins DESC
LIMIT 10;
"""
df = pd.read_sql(sql, con)
df

	name	wins	finishes	finish_pct
0	Vitor Belfort	15	14	93.3
1	Matt Brown	17	15	88.2
2	Vicente Luque	17	15	88.2
3	Joe Lauzon	15	13	86.7
4	Charles Oliveira	25	21	84.0
5	Anderson Silva	17	14	82.4
6	Frank Mir	16	13	81.3
7	Glover Teixeira	16	13	81.3
8	Derrick Lewis	20	16	80.0
9	Drew Dober	15	12	80.0

4. Do heavier fighters really knock people out more?

Technique: Conditional aggregation (pivot with CASE). Group bouts by weight class and, in one pass, compute the share ending by KO, submission, and decision. This pivots a category column into three metric columns.

sql = """\
SELECT weight_class,
       COUNT(*) AS bouts,
       ROUND(100.0*SUM(method_group='KO/TKO')  /COUNT(*),1) AS ko_pct,
       ROUND(100.0*SUM(method_group='Submission')/COUNT(*),1) AS sub_pct,
       ROUND(100.0*SUM(method_group='Decision') /COUNT(*),1) AS dec_pct
FROM bouts
WHERE weight_class IN ('Flyweight','Bantamweight','Featherweight','Lightweight',
      'Welterweight','Middleweight','Light Heavyweight','Heavyweight')
GROUP BY weight_class
ORDER BY bouts DESC;
"""
df = pd.read_sql(sql, con)
df

	weight_class	bouts	ko_pct	sub_pct	dec_pct
0	Lightweight	1465	30.0	21.7	47.2
1	Welterweight	1399	33.5	18.2	47.1
2	Middleweight	1154	38.0	21.1	39.3
3	Featherweight	871	29.5	16.5	52.9
4	Bantamweight	791	26.0	19.5	52.5
5	Heavyweight	768	51.3	14.2	32.7
6	Light Heavyweight	765	45.0	17.0	35.9
7	Flyweight	428	23.6	21.5	53.7

ax=df.set_index('weight_class')[['ko_pct', 'sub_pct', 'dec_pct']].plot.bar(color=[RED,PU,CY],figsize=(9,4))
ax.set_title("Do heavier fighters really knock people out more?"); ax.set_ylabel('percent of bouts'); ax.legend(['KO/TKO','Submission','Decision'],facecolor='#12121a',edgecolor='#2a2a3a',labelcolor='#e8e8ef'); plt.xticks(rotation=35,ha='right'); plt.tight_layout(); plt.show()

5. The longest win streaks in UFC history

Technique: Window functions (gaps and islands). The showpiece. Each bout is unpivoted to one row per fighter with a win flag, then two ROW_NUMBER() windows are subtracted so consecutive wins share an island id. Counting each island gives the streak length.

sql = """\
WITH per_fighter AS (
  SELECT b.bout_id, b.fighter1_id AS fid, e.date AS d,
         (b.winner_id = b.fighter1_id) AS won
  FROM bouts b JOIN events e ON e.event_id = b.event_id
  WHERE b.result_type = 'Win'
  UNION ALL
  SELECT b.bout_id, b.fighter2_id, e.date,
         (b.winner_id = b.fighter2_id)
  FROM bouts b JOIN events e ON e.event_id = b.event_id
  WHERE b.result_type = 'Win'
),
islands AS (
  SELECT fid, won,
         ROW_NUMBER() OVER (PARTITION BY fid          ORDER BY d, bout_id)
       - ROW_NUMBER() OVER (PARTITION BY fid, won     ORDER BY d, bout_id) AS grp
  FROM per_fighter
)
SELECT f.name, COUNT(*) AS win_streak
FROM islands
JOIN fighters f ON f.fighter_id = islands.fid
WHERE won = 1
GROUP BY fid, grp
ORDER BY win_streak DESC
LIMIT 10;
"""
df = pd.read_sql(sql, con)
df

	name	win_streak
0	Jon Jones	19
1	Islam Makhachev	17
2	Anderson Silva	16
3	Kamaru Usman	15
4	Merab Dvalishvili	14
5	Demetrious Johnson	13
6	Georges St-Pierre	13
7	Khabib Nurmagomedov	13
8	Max Holloway	13
9	Alexander Volkanovski	12

ax=df.iloc[::-1].plot.barh(x='name',y='win_streak',color=CY,legend=False,figsize=(8,4))
ax.set_title("The longest win streaks in UFC history"); ax.set_xlabel('consecutive wins'); plt.tight_layout(); plt.show()

6. The fastest finishes ever recorded

Technique: Multi-table JOIN + derived opponent. Join each bout to its winner and event, then join again to look up the loser (the other corner) with a CASE. Ordering by elapsed seconds surfaces the quickest stoppages.

sql = """\
SELECT f.name AS winner,
       l.name AS loser,
       b.method, b.time,
       ev.name AS event, ev.date
FROM bouts b
JOIN fighters f  ON f.fighter_id = b.winner_id
JOIN fighters l  ON l.fighter_id = CASE WHEN b.winner_id = b.fighter1_id
                                        THEN b.fighter2_id ELSE b.fighter1_id END
JOIN events   ev ON ev.event_id = b.event_id
WHERE b.method_group IN ('KO/TKO','Submission')
  AND b.round = 1 AND b.end_secs > 0
ORDER BY b.end_secs ASC
LIMIT 10;
"""
df = pd.read_sql(sql, con)
df

	winner	loser	method	time	event	date
0	Jorge Masvidal	Ben Askren	KO/TKO	0:05	UFC 239: Jones vs. Santos	2019-07-06
1	Duane Ludwig	Jonathan Goulet	KO/TKO	0:06	UFC Fight Night 3	2006-01-16
2	Chan Sung Jung	Mark Hominick	KO/TKO	0:07	UFC 140: Jones vs Machida	2011-12-10
3	Ryan Jimmo	Anthony Perosh	KO/TKO	0:07	UFC 149: Faber vs Barao	2012-07-21
4	Terrance McKinney	Matt Frevola	KO/TKO	0:07	UFC 263: Adesanya vs. Vettori 2	2021-06-12
5	Todd Duffee	Tim Hague	KO/TKO	0:07	UFC 102: Couture vs Nogueira	2009-08-29
6	Abdul Rakhman Yakhyaev	Julius Walker	KO/TKO	0:08	UFC Fight Night: Fiziev vs. Torres	2026-06-27
7	Don Frye	Thomas Ramirez	KO/TKO	0:08	UFC 8: David vs Goliath	1996-02-16
8	James Irvin	Houston Alexander	KO/TKO	0:08	UFC Fight Night: Florian vs Lauzon	2008-04-02
9	Leon Edwards	Seth Baczynski	KO/TKO	0:08	UFC Fight Night: Gonzaga vs Cro Cop 2	2015-04-11

7. Does a longer reach actually win fights?

Technique: Self-join + conditional aggregate. Join the bout to both fighters' tale-of-the-tape, keep bouts where reaches differ, and measure how often the longer-reach fighter won. A data-driven myth check.

sql = """\
WITH r AS (
  SELECT b.winner_id, b.fighter1_id, b.fighter2_id,
         f1.reach_in AS reach1, f2.reach_in AS reach2
  FROM bouts b
  JOIN fighters f1 ON f1.fighter_id = b.fighter1_id
  JOIN fighters f2 ON f2.fighter_id = b.fighter2_id
  WHERE b.result_type = 'Win'
    AND f1.reach_in IS NOT NULL AND f2.reach_in IS NOT NULL
    AND f1.reach_in <> f2.reach_in
)
SELECT COUNT(*) AS decided_bouts,
       ROUND(100.0*SUM(CASE WHEN (reach1>reach2 AND winner_id=fighter1_id)
                              OR (reach2>reach1 AND winner_id=fighter2_id)
                            THEN 1 ELSE 0 END)/COUNT(*),1) AS longer_reach_win_pct
FROM r;
"""
df = pd.read_sql(sql, con)
df

	decided_bouts	longer_reach_win_pct
0	6576	52.1

8. How the UFC exploded: bouts per year

Technique: Date bucketing + GROUP BY. Pull the year out of each event date and count bouts. A time series that tells the story of the sport growing from a curiosity in 1994 to hundreds of fights a year.

sql = """\
SELECT strftime('%Y', e.date) AS year,
       COUNT(*) AS bouts
FROM bouts b
JOIN events e ON e.event_id = b.event_id
WHERE e.date IS NOT NULL
GROUP BY year
ORDER BY year;
"""
df = pd.read_sql(sql, con)
df

	year	bouts
0	1994	31
1	1995	40
2	1996	43
3	1997	41
4	1998	25
5	1999	44
6	2000	43
7	2001	40
8	2002	53
9	2003	41
10	2004	39
11	2005	80
12	2006	158
13	2007	171
14	2008	201
15	2009	215
16	2010	253
17	2011	300
18	2012	341
19	2013	386
20	2014	503
21	2015	473
22	2016	493
23	2017	457
24	2018	474
25	2019	516
26	2020	456
27	2021	509
28	2022	511
29	2023	520
30	2024	517
31	2025	520
32	2026	364

d=df.copy(); d['year']=d['year'].astype(int)
ax=d.plot(x='year',y='bouts',marker='o',color=CY,legend=False,figsize=(8.5,4))
ax.set_title("How the UFC exploded: bouts per year"); ax.set_ylabel('bouts'); ax.grid(True,alpha=.25); plt.tight_layout(); plt.show()

9. The grinders: most octagon control time

Technique: JOIN to stats + SUM + HAVING. Sum control-time seconds from the per-fight stats table for each fighter, filtering to those with a real sample of fights. HAVING filters on the aggregate, which WHERE cannot do.

sql = """\
SELECT f.name,
       ROUND(SUM(s.ctrl_secs)/60.0) AS control_minutes,
       COUNT(*) AS fights
FROM fight_stats s
JOIN fighters f ON f.fighter_id = s.fighter_id
GROUP BY s.fighter_id
HAVING fights >= 12
ORDER BY control_minutes DESC
LIMIT 10;
"""
df = pd.read_sql(sql, con)
df

	name	control_minutes	fights
0	Georges St-Pierre	162.0	22
1	Clay Guida	156.0	37
2	Demian Maia	155.0	33
3	Kamaru Usman	147.0	20
4	Rafael Dos Anjos	137.0	36
5	Darren Elkins	129.0	31
6	Jon Fitch	128.0	18
7	Randy Couture	127.0	24
8	Colby Covington	125.0	17
9	Valentina Shevchenko	122.0	20

ax=df.iloc[::-1].plot.barh(x='name',y='control_minutes',color=CY,legend=False,figsize=(8,4))
ax.set_title("The grinders: most octagon control time"); ax.set_xlabel('control minutes'); plt.tight_layout(); plt.show()

Takeaways

• Modeling first. A flat scrape became four linked tables. That single design choice is what turns a pile of CSV rows into something you can ask questions of.
• The right tool per question. Counting wins is a join and a group by. Finish rate needs a CASE. Streaks need window functions. Reach needs a self-join. Matching the SQL pattern to the question is the core skill.
• The findings are real and recognizable. The most UFC wins, the longest streaks, the fastest finishes, and the fact that a reach advantage wins only about half the time all come straight out of the data, not out of assumptions.

The same database is live on the dashboard, where anyone can run these queries or write their own.

Data: ufcstats.com (scraped, public). Schema design, queries, and visuals by AbleVLabs.

con.close()