Select count distinct on key column where multiple others satisfy condition

Question

I have a table where I am trying to find a count distinct on a column that is not a the primary key

account_id | media_id | sat_cond1 | sat_cond2
---------------------------------------------
123        |  333     |   Y       |     N
123        |  334     |   N       |     Y
123        |  335     |   N       |     N
124        |  221     |   N       |     Y
124        |  222     |   N       |     N
125        |  111     |   Y       |     Y

I'm trying to figure out a query that will return a count of account_ids where there's at least one row that satisfies sat_cond1 and at least one row that satisfies sat_cond2.

For instance, in the above table account_id 123 would, because media_id 333 satisfies sat_cond1 and 334 satisfies sat_cond2. account_id 124 would not be counted, however, because though media_id 221 satisfies sat_cond2 there is no media_id that satisfies sat_cond1 for it. account_id 125 meets all conditions on a single media_id, so it would be counted.

What sort of query would give me this?

Answer 1

Create groups with GROUP BY based on account_id. Find the MAX of sat_cond1 and sat_cond2 for each group. If the MAX values equal Y for both condition, keep the account_id. Count the distinct account_id values.

Above translated to SQL:

select
  count(*)
from
  (
    select
      account_id
    from 
      data
    group by
      account_id
    having
      max(sat_cond1) = 'Y'
      and max(sat_cond2) = 'Y'
  )
;