How to write queries that work the first time. Every example below is a real query you can paste into the NFL or NCAAF query tool — click any example to copy it. For the full list of fields and macros, see the NFL and NCAAF references.
Every row in the database is one team in one game. A query is a list of conditions a row must meet.
Join them with and / or (and binds tighter than or), flip one with not, and group with parentheses.
The tool then shows how those teams did against the spread, straight up, and over/under.
9.5>=line>=3 means the same thing.= != < <= > >=. Text goes in quotes: team='DAL'.week in [1,2,3].H home, A away, N neutral site, W won the game (straight up), L lost the game. To ask about covering the spread, use atsmargin>0.snf, or not snf.A season call looks at a team's whole season and boils it down to one number. It has three parts:
the letter t (this team), an optional marker for which season, and what to calculate.
| Calculate | Letter | Meaning |
|---|---|---|
| Total | S | Sum — or a count when you give it a condition like W |
| Average | A | Average — or the fraction (0–1) of games when you give it a condition |
| Highest | MAX | The biggest single value |
| Lowest | MIN | The smallest single value |
| Marker | Which games | Example |
|---|---|---|
| (none) | The entire current season — including this game and games after it | tA(points) |
b | This season, but only games before this one | tbA(points) |
p | Last season | tpA(points) |
pp | Two seasons back | tppA(points) |
tpA(W)*100>=65 asks for a 65%+ win rate instead.)wins — adding up a counter overcounts.S gives a count: tS(W)>=3 means 3 or more wins. A gives a fraction from 0 to 1: tA(W)>=0.6 means a 60% win rate. Writing tA(W)>=6 can never match.b markerA plain tS(W) counts the whole season, including games that hadn't happened yet when this row's game was played.
For a betting system you almost always want what the team knew going into the game. Add b:
tS(W)=0 would mean winless the entire season — hindsight.)The first game of a season has no earlier games, so b conditions never match it. That's intended — there's nothing to measure yet.
Add ,N=k inside any season call to look at only the last k games — "how is this team playing lately."
The window is the k games played before the current game, so it's safe to use as a pregame filter.
o: reaches the opponent's score in each of those games).N=4 in Week 3 averages the 2 earlier games).p, the window is the last k games of last season: tpA(points,N=3)>=30 = averaged 30+ over the final 3 games of last season.b is optional here — tbA(points,N=4) and tA(points,N=4) mean the same thing.tA(points,N=3)>oA(points,N=3) — this team's recent scoring beats the opponent's.Put a whole condition inside S or A to count or take the fraction of games that meet it.
*100 to use percentages: tA(W,N=5)*100>=60.Ready-made macros cover the common ones: SUW, SUL, ATSW, ATSL, SUPCT, ATSPCT and more, all "entering this game."
Put o in front of a season call to measure today's opponent instead of this team: oA(points) is what the opponent scores per game.
Use o: inside the parentheses to look at what a team's opponents did: tA(o:points) is what this team allows.
Prefixes with a colon reach a specific other game instead of a season: p: the previous game, pp: two games back (or p3: for three),
n: the next game, o: the opponent in this same game, op: the opponent's previous game, P: the last meeting between these two teams.
Prefix lookups follow the team's whole schedule, so in Week 1 p: means last season's final game.
Rolling windows and tb calls stay inside the current season.
tS(W)=0 to mean "winless so far." Without b it counts the whole season, including later games. Use tbS(W)=0.wins already accumulate through the season. Use tpMAX(wins), not tpS(wins).S counts (>=3); A is 0–1 (>=0.6) or 0–100 with *100.tA(points,N=4) in Week 2 averages only the one earlier game, and Week 1 never matches. For last season's tail use tpA(points,N=4).