SELECT 
    bl.name as location_name,
    u.first_name as employee_name,
    COUNT(DISTINCT t.id) as transaction_count,
    SUM(t.final_total) as total_sales,
    AVG(t.final_total) as avg_sale_value,
    SUM(t.final_total)/COUNT(DISTINCT DATE(t.transaction_date)) as daily_avg_sales
FROM business_locations bl
JOIN transactions t ON bl.id = t.location_id
JOIN users u ON t.created_by = u.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, u.id, u.first_name
ORDER BY bl.name, total_sales DESC;