How to use an output of a query as a where parameter in a second query
I have this query to list top_10
lead 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?
Solution 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