How to use an output of a query as a where parameter in a second query

Question

I have this query to list top_10lead segments:

select segment as "Segment", count(*) as "Qty" from table
where id >=1
group by segment
order by 2 desc
limit 10

Now I want to use the first segment in the query above (limit 1 desc) to use in a where clause to list all roles from that particular segment. Let's say the top_1 segment in this first query was aviation`.

Then I want to query like this:

select role, count(*) from table
where segment = 'aviation'
group by role
order by 2 desc

How can I do this?

Answer 1

You can use analytic count function to get counts per segment, then get top segment using first_value, then filter. See comments in the code:

select role, count(*)
from
(
select segment, role,
       --Get Top segment
       first_value(segment) over(order by segment_cnt desc rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING ) as top_segment
from
    (
     select --calculate segment counts
           segment, role, 
           count(case when id>=1 then 1 else null end) over (partition by segment) as segment_cnt
      from table
    )s
)s
WHERE segment=top_segment --filter only top 1 segment
GROUP BY role
ORDER BY 2 desc