SELECT 
    HOUR(t.transaction_date) as hour_of_day,
    COUNT(DISTINCT t.id) as transaction_count,
    SUM(tsl.quantity) as total_quantity_sold,
    SUM(t.final_total) as total_revenue,
    AVG(t.final_total) as avg_transaction_value
FROM transactions t
JOIN transaction_sell_lines tsl ON t.id = tsl.transaction_id
WHERE t.type = "sell" 
AND t.status = "final"
AND t.transaction_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
GROUP BY HOUR(t.transaction_date)
ORDER BY hour_of_day;