code used

--I queried the dataset to visualize and understand how price affects quantity sold.
SELECT
	price,
	quantity_sold
FROM demand_curve_data.csv;

--Calculated for a regression function
WITH regression_data AS (
SELECT 
	stddev(quantity_sold) AS sd_quantity_sold,
	avg(quantity_sold) AS mean_quantity_sold,
	avg(price) AS mean_price,
	stddev(price) AS sd_price,
	corr(price, quantity_sold) AS corr_price_quantity
FROM demand_curve_data.csv)
	SELECT 
	corr_price_quantity * (sd_quantity_sold / sd_price) AS slope,
	mean_quantity_sold - (corr_price_quantity * (sd_quantity_sold / sd_price) * mean_price) AS intercept
FROM regression_data;

---DuckDB provides built-in regression functions that make this easy:
SELECT 
	regr_slope(quantity_sold,price) AS slope,
	regr_intercept(quantity_sold,price) AS intercept
FROM demand_curve_data.csv;

SELECT
	price,
	LN(quantity_sold)
FROM demand_curve_data.csv;

---Built a regression function for nonlinear models (log-linear demand)
SELECT
	price,
	EXP(
				(SELECT regr_slope(LN(quantity_sold),price) 
					FROM demand_curve_data.csv) * price +
	            (SELECT regr_intercept(LN(quantity_sold),price) 
					FROM demand_curve_data.csv) 
		) * price AS revenue_function
FROM demand_curve_data.csv;

---Compute the optimal price using calculus
WITH regression_data AS (
    SELECT 
        regr_slope(LN(quantity_sold), price) AS slope,
        regr_intercept(LN(quantity_sold), price) AS intercept
    FROM demand_curve_data.csv
)
SELECT 
    (-1 / slope) AS optimal_price,
    ((-1 / slope) * EXP(intercept - 1)) AS max_revenue
FROM regression_data;

---To test our answer, we could try a more naive way of finding the best price
SELECT
    price, 
    price * quantity_sold AS revenue
FROM "demand_curve_data.csv"
ORDER BY revenue DESC
LIMIT 1