WITH RankedSales AS (
    SELECT 
        category_id, 
        product_name, 
        revenue,
        ROW_NUMBER() OVER (
            PARTITION BY category_id 
            ORDER BY revenue DESC, product_name ASC
        ) as rank_id
    FROM SALES
)
SELECT category_id, product_name, revenue
FROM RankedSales
WHERE rank_id <= 2;

---
  
  SELECT DISTINCT a.user_id
FROM USER_LOGINS a
JOIN USER_LOGINS b 
  ON a.user_id = b.user_id 
  AND a.login_date = b.login_date - INTERVAL '1 day'
ORDER BY a.user_id;

---
  
  SELECT 
    e.name, 
    COALESCE(b.bonus_amount, 0) AS final_bonus
FROM EMPLOYEES e
LEFT JOIN BONUSES b ON e.emp_id = b.emp_id
ORDER BY final_bonus DESC;


---
  
  SELECT MAX(tour_count) AS max_eligible_tours
FROM (
    SELECT 
        f.ID, 
        COUNT(c.ID) AS tour_count
    FROM FAMILIES f
    LEFT JOIN COUNTRIES c 
        ON f.FAMILY_SIZE BETWEEN c.MIN_SIZE AND c.MAX_SIZE
    GROUP BY f.ID
) AS family_eligibility;