SELECT 
    CASE 
        WHEN tsl.line_discount_amount > 0 THEN "Promotional"
        ELSE "Regular"
    END as sale_type,
    COUNT(DISTINCT t.id) as number_of_transactions,
    SUM(tsl.quantity) as total_quantity_sold,
    SUM(tsl.unit_price_inc_tax * tsl.quantity) as gross_revenue,
    SUM(tsl.line_discount_amount) as total_discount,
    SUM((tsl.unit_price_inc_tax * tsl.quantity) - tsl.line_discount_amount) as net_revenue,
    AVG(tsl.line_discount_amount) as avg_discount_per_transaction
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 CASE WHEN tsl.line_discount_amount > 0 THEN "Promotional" ELSE "Regular" END;