How to get first and last values instead of averages in SQL GROUP BY query

Data and Databases
1

I’m working on a SQL query that groups financial data by time intervals, but I’m having trouble getting the correct opening and closing values. Right now my query is using AVG() which gives me average values, but what I actually need is the opening price from the earliest timestamp in each group and the closing price from the latest timestamp in each group.

Here’s my current query:

SELECT
    DATEADD(MINUTE, DATEDIFF(MINUTE, 0, trades.timestamp) / 10 * 10, 0) AS [TimeGroup],
    AVG(trades.[opening_price]) AS [OpenPrice], -- Need first value by date instead
    MAX(trades.[high_price]) AS [HighPrice],
    MIN(trades.[low_price]) AS [LowPrice],
    AVG(trades.[closing_price]) AS [ClosePrice], -- Need last value by date instead
    SUM(trades.[trade_volume]) AS [TotalVolume]
FROM
    (SELECT TOP (5000) * FROM [dbo].[ethereum_trades]) AS trades
GROUP BY
    DATEADD(MINUTE, DATEDIFF(MINUTE, 0, trades.[timestamp]) / 10 * 10, 0)
ORDER BY
    TimeGroup

The issue is with the opening and closing price calculations. Instead of averaging all values in the time group, I need to pick the opening price from the record with the minimum timestamp and the closing price from the record with the maximum timestamp within each group. How can I modify this query to achieve that?

2

u can use window functions like FIRST_VALUE and LAST_VALUE to get the prices. just replace AVG with these in ur query. like FIRST_VALUE(opening_price) OVER (PARTITION BY TimeGroup ORDER BY timestamp) for open, and LAST_VALUE(closing_price) OVER (PARTITION BY TimeGroup ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) for close. might need a subquery too.

3

Interesting challenge! What about using correlated subqueries? Try somthing like (SELECT opening_price FROM trades t2 WHERE t2.timestamp = MIN(trades.timestamp) AND same_timegroup) for the first value. Quick question though - does the earliest record alwais have the real opening price for that interval? Could there be data ordernig problems?

4

Use conditional aggregation with MIN and MAX functions instead of AVG. For opening price, try MIN(CASE WHEN trades.timestamp = (SELECT MIN(t2.timestamp) FROM trades t2 WHERE DATEADD(MINUTE, DATEDIFF(MINUTE, 0, t2.timestamp) / 10 * 10, 0) = TimeGroup) THEN trades.opening_price END). For closing price, just swap MAX for MIN in the subquery condition. This grabs the price from the actual record with the earliest or latest timestamp in each group. You’ll avoid messy window functions and keep your current query structure. The conditional aggregation handles cases where multiple records have the same min/max timestamp - you’ll still get one value per group.