How can I take the difference in values between the output of two SQL queries?

Question

The following PostgreSQL query:

SELECT REFERENCE_DATE, MIC, INSTRUMENT_TYPE, COUNT(INSTRUMENT_TYPE) as counter1
FROM reference
WHERE 
reference_date = CURRENT_DATE() -1 and
--AND 
MIC = 'TWEM' and INSTRUMENT_TYPE='Equity'
GROUP BY REFERENCE_DATE, MIC, INSTRUMENT_TYPE;

Returns

   REFERENCE_DATE MIC  INSTRUMENT_TYPE  COUNTER1
    2025-01-22  TWEM    Equity           13540
    2025-02-17  TWEM    Equity           13629
    2025-02-13  TWEM    Equity           13620
    2025-01-27  TWEM    Equity           13545
    2025-01-31  TWEM    Equity           13566
    2025-02-24  TWEM    Equity           13636
    2025-01-21  TWEM    Equity          13538
    2025-01-15  TWEM    Equity          13526
    2025-02-04  TWEM    Equity          13577
    2025-01-28  TWEM    Equity          13548
    2025-02-12  TWEM    Equity          13619

And if I change the reference_date above to CURRENT_DATE() - 2 it will produce another row similar to the above.

What I am trying to do is get the difference in the COUNTER1 column between two days - any ideas how I can do this?

Answer 1

You can use a Common Table Expression to make your query's result appear as a temporary table,
then walk through that temporary table with a window function that allows a row to access values from the previous row.

WITH raw_query AS
(
    -- This is your query:
    SELECT REFERENCE_DATE, MIC, INSTRUMENT_TYPE, COUNT(INSTRUMENT_TYPE) as counter1
    FROM reference
    GROUP BY REFERENCE_DATE, MIC, INSTRUMENT_TYPE
)
SELECT
    *,
    -- Now add the difference:
    -- From the counter1 (of the current row), subtract the counter1 of the previous row (previous among the rows with the same MIC and INSTRUMENT_TYPE; and ordered by REFERENCE_DATE within that group)
    counter1 - LAG(counter1) OVER (PARTITION BY MIC, INSTRUMENT_TYPE ORDER BY REFERENCE_DATE) AS difference
FROM raw_query -- The results of the previous query appear as a table named raw_query.
ORDER BY MIC, INSTRUMENT_TYPE, REFERENCE_DATE;

Here would be the results given the imaginary counter1 you can see included as a a column:

reference_date	mic	instrument_type	counter1	difference
2025-03-04	TWEM	Equity	13650	null
2025-03-05	TWEM	Equity	13469	-181
2025-03-06	TWEM	Equity	13216	-253
2025-03-07	TWEM	Equity	13348	132

You can see it emulated in a fiddle.

Filtering the results of a window function

As there is no such thing as a HAVING for window functions as there is for GROUP BY,
to further filter the result set you'll have to make it another CTE,
which in turn will be filtered for the final result:

WITH raw_query AS
(
    […]
),                         -- <- add a comma here, because another CTE follows. 
all_results AS             -- <- wrap the previous final query in a CTE
(
    SELECT
        *,
        -- Now add the difference:
        […]
    ORDER BY MIC, INSTRUMENT_TYPE, REFERENCE_DATE
)
SELECT * FROM all_results  -- <- now select from that last CTE
WHERE ABS(difference) > 20 -- <- and filter on it