is there a way to use the result of the LAG() function to LAG() that same column

Question

I'm trying to create frequency buckets in sql, I'm able to calculate the number of bins and the bin width. I have a table like this:

Zip	Bin_Width	Bin_Count	Min_Px	Max_Px
07302	289285.717	7	325000.00	2350000.00

I want to create a column or seperate table that creates the bin widths until the count is 7 (since their are only 7 bins) which would look like this, the math is taking the first rows min_px + bin width and then take that value + bin_width.

Zip	Lower_Bound	Upper Bound	Math
07302	325000	614285.7143	Lower_bound + bin_with (289285.717) = Upper Bound
07302	614285.7143	903571.4286	Lower_bound + bin_with (289285.717) = Upper Bound
07302	903571.4286	1192857.143	Lower_bound + bin_with (289285.717) = Upper Bound
07302	1192857.143	1482142.857
07302	1482142.857	1771428.571
07302	1771428.571	2060714.286
07302	2060714.286	2350000

It unclear to me where to start, I know I need to use the LAG() function but unclear how to use the LAG to LAG that value

Answer 1

Please check generate_series option in postgresql . since you didn't tag or mention any database system. Here is the ddl and sql statment.

create table stack_questions_100
(zip varchar(20),
Bin_Width   decimal(10,3),
Bin_Count int,
min_px decimal(10,3),
max_px decimal(10,3)
);


insert into stack_questions_100
values
('07302',289285.717,7,325000.00,2350000.00);

select * from stack_questions_100;



 select 
    zip , s.min_px+((id -1 ) * bin_width) as lower_bound,s.min_px+(id * bin_width ) as upper_bound
    
    from stack_questions_100 s,
    generate_series(1,s.Bin_Count) as temp(id)

output

 zip  |lower_bound|upper_bound|
-----+-----------+-----------+
07302| 325000.000| 614285.717|
07302| 614285.717| 903571.434|
07302| 903571.434|1192857.151|
07302|1192857.151|1482142.868|
07302|1482142.868|1771428.585|
07302|1771428.585|2060714.302|
07302|2060714.302|2350000.019|