FREE AI-Enhanced Online PostgreSQL Compiler

Untitled*

You must execute the code again to save those values in the database.

create table Contract_Base (ID int, Part int, Qty_Commit float, Date date, StartDate date, EndDate date, Percentage text, Pro_Rate float);
insert into Contract_Base values
	(10002, 200750, 550, '10/1/2024', '10/1/2024', '10/1/2025', '40%', 220),
	(10002, 200750, 600, '2/24/2025', '10/1/2024', '10/1/2025', '0%', 1.64),
	(10002, 200750, 750, '2/25/2025', '10/1/2024', '10/1/2025', '8%', 59.59),
	(10002, 200750, 750, '3/26/2025', '10/1/2024', '10/1/2025', '11%', 82.19),
	(10002, 200750, 800, '5/5/2025', '10/1/2024', '10/1/2025', '7%', 54.79),
	(10002, 200750, 800, '5/30/2025', '10/1/2024', '10/1/2025', '13%', 105.21),
	(10002, 200750, 900, '7/17/2025', '10/1/2024', '10/1/2025', '21%', 187.40);
alter table Contract_Base drop Percentage, drop Pro_Rate;

-- Emulate datediff of Databricks SQL:
create function datediff(s date, e date) returns int language sql as
$$
	select e - s;
$$;
create function round(n float, prec int) returns float language sql as
$$
	select round(n * pow(10, prec))::int / pow(10, prec);
$$;

SELECT
    ID,
    Part,
    Qty_Commit,
    Date,
    StartDate,
    EndDate,
    100 * DATEDIFF(date, COALESCE(LEAD(date) OVER (PARTITION BY id, part ORDER BY date), enddate)) / DATEDIFF(startdate, enddate) AS Percentage,
    ROUND(Qty_Commit * DATEDIFF(date, COALESCE(LEAD(date) OVER (PARTITION BY id, part ORDER BY date), enddate)) / DATEDIFF(startdate, enddate), 2) AS Pro_Rate
FROM Contract_Base;

id	part	qty_commit	date	startdate	enddate	percentage	pro_rate
10002	200750	550	2024-10-01	2024-10-01	2025-10-01	40	220
10002	200750	600	2025-02-24	2024-10-01	2025-10-01	0	1.64
10002	200750	750	2025-02-25	2024-10-01	2025-10-01	7	59.59
10002	200750	750	2025-03-26	2024-10-01	2025-10-01	10	82.19
10002	200750	800	2025-05-05	2024-10-01	2025-10-01	6	54.79
10002	200750	800	2025-05-30	2024-10-01	2025-10-01	13	105.21
10002	200750	900	2025-07-17	2024-10-01	2025-10-01	20	187.4

CREATE TABLE
INSERT 0 7
ALTER TABLE
CREATE FUNCTION
CREATE FUNCTION