Lessons › SQL › Deep Dive: Business Analytics Patterns

SQL lesson

Trend lines with REGR_SLOPE() and REGR_INTERCEPT()

REGR_SLOPE(y, x) says how much y changes for each 1 more of x. REGR_INTERCEPT(y, x) is where that straight line starts. Together they draw the trend line.

Example

How much do sales change per dollar?

SELECT REGR_SLOPE(units_sold, price) AS slope,
       REGR_INTERCEPT(units_sold, price) AS intercept
FROM products

Result:

Step by step

1. How many units sales change for each $1 of price. Negative: higher price, fewer sales.

   SELECT REGR_SLOPE(units_sold, price) AS slope
   FROM products;

2. Where the line starts: the trend's units sold at a price of 0.

   SELECT REGR_SLOPE(units_sold, price) AS slope,
          REGR_INTERCEPT(units_sold, price) AS intercept
   FROM products;

3. Predicted units = intercept + slope × price. Use it to test a price change before you make it.

   SELECT REGR_SLOPE(units_sold, price) AS slope,
          REGR_INTERCEPT(units_sold, price) AS intercept
   FROM products;

Practice

Try each one in your head first, then open the answer.

SELECT REGR_SLOPE(units_sold, price) AS slope FROM products

SELECT REGR_INTERCEPT(units_sold, price) AS intercept FROM products

SELECT REGR_SLOPE(units_sold, price) AS slope, REGR_INTERCEPT(units_sold, price) AS intercept FROM products

SELECT REGR_SLOPE(units_sold, rating) AS slope FROM products

SELECT REGR_INTERCEPT(units_sold, price) + REGR_SLOPE(units_sold, price) * 100 AS predicted_units FROM products