In SQL, r-squared over the sample table is calculated with the query below. On the sample data it returns 0.52.
R² = 1 − Σ (y − ŷ)² ÷ Σ (y − ȳ)² (for one predictor, the square of r)
Step by step
- Load daily.csv into a table called daily (day, ad_spend, website_orders, app_orders, avg_basket).
- Run the query and compare with the expected result.
CREATE TABLE daily (
day INTEGER,
ad_spend INTEGER,
website_orders INTEGER,
app_orders INTEGER,
avg_basket INTEGER
);The query
SELECT ROUND(REGR_R2(website_orders, ad_spend), 3) AS r_squared
FROM daily;Expected result on the sample data: 0.52.
What the result means
A high R² does not make a model useful, and a low one does not make it useless: a factor that explains a small share of a large number can still be worth a lot.
Notes for SQL
- Tested on DuckDB. The same functions exist in PostgreSQL. SQLite and MySQL lack most statistical aggregates; compute in Python or load the data into DuckDB, which can query a CSV file directly.
- Statistical aggregates ignore NULL rows. Decide whether a missing day should be NULL (left out) or 0 (counted as a zero day).
Where r-squared misleads
- Adding predictors always raises R²; use adjusted R² when comparing models with different numbers of inputs.
- One extreme point (day 17 here) can produce most of the R².
- R² says nothing about whether the relationship is causal.
The plain-language explanation is on the R-squared page.
The data
| day | ad_spend | website_orders |
|---|---|---|
| 1 | 3,800 | 24 |
| 2 | 3,000 | 27 |
| 3 | 3,900 | 22 |
| 4 | 3,200 | 30 |
| 5 | 4,000 | 28 |
| 6 | 4,100 | 25 |
| 7 | 3,100 | 31 |
| 8 | 3,600 | 29 |
| 9 | 4,100 | 26 |
| 10 | 3,500 | 33 |
| 11 | 4,300 | 30 |
| 12 | 4,500 | 28 |
| 13 | 3,900 | 35 |
| 14 | 3,900 | 32 |
| 15 | 4,300 | 29 |
| 16 | 3,700 | 34 |
| 17 | 7,200 | 61 |
| 18 | 4,800 | 31 |
| 19 | 3,800 | 36 |
| 20 | 4,500 | 33 |
| 21 | 4,500 | 30 |
| 22 | 4,000 | 37 |
| 23 | 4,200 | 35 |
| 24 | 4,800 | 32 |
| 25 | 4,300 | 38 |
| 26 | 5,000 | 36 |
| 27 | 3,900 | 34 |
| 28 | 5,000 | 40 |
| 29 | 5,200 | 37 |
| 30 | 4,700 | 39 |
Same method, other tools
Related methods
Learn more about SQL
- Python and SQL for data analysis, side by side — 3 min read
- Data analysis methods and techniques, and when to use each — 3 min read
About the data on this page
The figures come from a small made-up dataset, printed above in full so you can check every number by hand. Download it as daily.csv. The results are computed from those rows when the page is built, and the Excel formulas, SQL and Python on these pages are run against the same rows before publishing, so the code and the answers agree.
To get these numbers from your own data without writing formulas, try AnalyzeIt: ask in plain words and it shows the working behind every figure.
Legal entity
Devansh Yadav
Moradabad, Uttar Pradesh
Governing law: Courts at Moradabad, Uttar Pradesh
Phone: +91 8433475698
Legal: legal@analyzeit.in