price_overview Table

-- Fast lookup for latest item prices
CREATE INDEX idx_overview_item_time
  ON price_overview(market_hash_name, timestamp DESC);

-- Time-based queries
CREATE INDEX idx_overview_timestamp
  ON price_overview(timestamp DESC);

-- App-specific queries
CREATE INDEX idx_overview_appid
  ON price_overview(appid, market_hash_name, timestamp DESC);

SELECT timestamp, lowest_price, median_price, volume
FROM price_overview
WHERE market_hash_name = 'AK-47 | Redline (Field-Tested)'
ORDER BY timestamp DESC
LIMIT 1;

SELECT timestamp, lowest_price, median_price
FROM price_overview
WHERE market_hash_name = 'AK-47 | Redline (Field-Tested)'
  AND timestamp > datetime('now', '-1 hour')
ORDER BY timestamp DESC;

SELECT market_hash_name,
       MAX(timestamp) AS last_update,
       lowest_price,
       volume
FROM price_overview
GROUP BY market_hash_name
ORDER BY volume DESC;

SELECT 
    date(timestamp) AS day,
    AVG(volume) AS avg_daily_volume,
    MAX(volume) AS peak_volume
FROM price_overview
WHERE market_hash_name = 'AK-47 | Redline (Field-Tested)'
  AND timestamp > datetime('now', '-7 days')
GROUP BY date(timestamp)
ORDER BY day DESC;