Which department ranks third in revenue?

Use a window function to rank departments by revenue and then filter for rank 3. Example:

WITH dept_rev AS (

SELECT v.department,

     SUM(b.amount) AS revenue,
     ROW_NUMBER() OVER (ORDER BY SUM(b.amount) DESC) AS rn

FROM visits v

JOIN billing b ON v.visit_id = b.visit_id

GROUP BY v.department

)

SELECT department, revenue

FROM dept_rev

WHERE rn = 3;

This returns the department that has the third highest total revenue.