SELECT 
    COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed_count,
    COUNT(CASE WHEN status = 'canceled' THEN 1 END) AS canceled_count
FROM orders;

SELECT 
    user_id,
    SUM(CASE WHEN is_new = 1 THEN amount ELSE 0 END) AS new_customer_revenue,
    SUM(CASE WHEN is_new = 0 THEN amount ELSE 0 END) AS existing_customer_revenue
FROM sales
GROUP BY user_id;

SELECT
    COUNT(CASE 
        WHEN score >= 90 THEN 1 
    END) AS A_grade,
    COUNT(CASE 
        WHEN score >= 80 AND score < 90 THEN 1 
    END) AS B_grade,
    COUNT(CASE 
        WHEN score >= 70 AND score < 80 THEN 1 
    END) AS C_grade,
    COUNT(CASE 
        WHEN score < 70 THEN 1 
    END) AS F_grade
FROM students;

SELECT
    department,
    AVG(CASE WHEN gender = 'M' THEN salary END) AS avg_male_salary,
    AVG(CASE WHEN gender = 'F' THEN salary END) AS avg_female_salary
FROM employees
GROUP BY department;