SELECT 
    bl.name as location_name,
    HOUR(t.transaction_date) as hour_of_day,
    COUNT(*) as transaction_count,
    AVG(t.final_total) as avg_transaction_value,
    SUM(t.final_total) as total_revenue
FROM business_locations bl
JOIN transactions t ON bl.id = t.location_id
WHERE t.type = "sell" 
AND t.status = "final"
AND t.transaction_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
GROUP BY bl.id, bl.name, HOUR(t.transaction_date)
ORDER BY bl.name, hour_of_day;