postgresql select rows from same table twice

Question

I want to compare deposit for each person in the table. and return all the rows where the deposit field is decreased.

Here is what I have done so far;

The customer table is;

   person_id  employee_id  deposit  ts 
    101        201         44        2021-09-30 10:12:19+00
    100        200         45        2021-09-30 10:12:19+00
    101        201         47        2021-09-30 09:12:19+00
    100        200         21        2021-09-29 10:12:19+00
    104        203         54        2021-09-27 10:12:19+00

and as a result I want is;

   person_id  employee_id  deposit  ts 
    101        201         44        2021-09-30 10:12:19+00

SELECT person_id, 
       employee_id,
       deposit,
       ts,
       lag(deposit) over client_window as pre_deposit,
       lag(ts) over client_window as pre_ts
FROM customer 
WINDOW client_window as (partition by person_id order by ts)
ORDER BY person_id , ts

so it returns table with the following results;

   person_id  employee_id  deposit  ts                       pre_deposit   pre_ts
    101        201         44        2021-09-30 10:12:19+00    47          2021-09-30 09:12:19+00 
    100        200         45        2021-09-30 10:12:19+00    21          2021-09-29 10:12:19+00
    101        201         47        2021-09-30 09:12:19+00    null        null 
    100        200         21        2021-09-29 10:12:19+00    null        null
    104        203         54        2021-09-27 10:12:19+00    null        null


SELECT person_id, 
       employee_id,
       deposit,
       ts,
       lag(deposit) over client_window as pre_deposit,
       lag(ts) over client_window as pre_ts
FROM customer 
WINDOW client_window as (partition by person_id order by ts)
WHERE pre_deposit > deposit //this returns column not found for pre_deposit
ORDER BY person_id , ts

so far somehow I need to select the same table again to be able to apply this condition;

where pre_deposit > deposit

what does it make sense here? union? outer-join? left-join? right-join?

score 0 · Answer 1 · answered Oct 01 '21 at 17:08

Use your query as a subquery and filter the results:

SELECT person_id, employee_id, deposit, ts
FROM (
  SELECT *, lag(deposit) over client_window as pre_deposit
  FROM customer 
  WINDOW client_window as (partition by person_id order by ts)
) t
WHERE deposit < pre_deposit 
ORDER BY person_id, ts;

See the demo.

postgresql select rows from same table twice

1 Answers1