I call on the SQL Gods of the internet!! O so desperately need your help with this query, my livelyhood depends on it. I've solved it in Alteryx in like 2 minutes but i need to write this query in SQL and I am relatively new to the language in terms of complex blending and syntax. Your help would be so appreciated!! :) xoxox I cant begin to describe
Using SSMS I need to use 2 tables 'searches' and 'events' to query...
the TOP 2 [user]s with the highest count of unique search ids in Table 'searches'
Condition that the [user]s in the list have at least 1 eventid in 'events' where [event type] starts with "great"
Here is an example of what needs to happen
search event and end result example
So the only pieces i have so far are below but boy oh boy please don't Laugh :(
What i was trying to do is..
select a table of unique users with the searchcounts from the search table
inner join selected table from 1 on userid with a table described in 3
create table of unique user ids with counts of events with [type] starting with "great"
Filter the inner joined table for the top 2 search counts from step 1
SELECT userid, COUNT() as searchcount FROM searches GROUP BY userid INNER JOIN (SELECT userid, COUNT() as eventcount FROM events WHERE LEFT(type, 5) = "great" AND eventcount>0 Group by userid) ON searches.userid=events.userId
Obviously, this doesn't work at all!!! I think my structure is off and my method of filtering for "great" is errored. Also i dont know how to add the "top 2" clause to the search table query without affecting the inner join. This code needs to be fairly efficient so if you have a better more computationally efficient idea...I love you long time
SELECT top(2) userid, COUNT() as searchcount FROM searches
where userid in (select userid from events where left(type, 5)='great')
GROUP BY userid
order by count() desc
hope above query will serve your purpose.