Advanced Queries

:CTE active_users AS (GET User WHERE active = true)

:SUBQUERY id IN (GET User WHERE active = true)

:SUBQUERY user_id IN (GET Order WHERE total > 100)

:EXISTS (GET User WHERE email = "john@example.com")

:UNION (GET User WHERE age > 50) (GET User WHERE role = "premium")

:UNION ALL (GET User WHERE department = "sales") (GET User WHERE department = "marketing")

:INTERSECT (GET User WHERE age > 30) (GET User WHERE active = true)

:EXCEPT (GET User WHERE active = true) (GET User WHERE role = "banned")

:GET User WITH CASE WHEN age > 25 THEN "adult" ELSE "minor" END AS category

:GET User WITH CASE 
  WHEN age < 18 THEN "minor" 
  WHEN age < 65 THEN "adult" 
  ELSE "senior" 
END AS age_group

:GET Order WHERE CASE WHEN total > 1000 THEN "large" ELSE "small" END = "large"

:CTE premium AS (GET User WHERE role = "premium" AND active = true)

:EXCEPT (GET User WHERE active = true) (SUBQUERY user_id IN (GET Order WHERE created_at > "2024-01-01"))

:GET Product WITH 
  name, 
  price, 
  CASE 
    WHEN price < 10 THEN "budget" 
    WHEN price < 100 THEN "standard" 
    ELSE "premium" 
  END AS tier