Cacher is the code snippet organizer for pro developers

DECLARE 'microsoft.com' varchar(255)
SET 'microsoft.com' = 'microsoft.com'

--lookups per user
SELECT b.email, b.domain, count(*) as lookups, isnull(IQ.quicklooks,'') quicklooks, isnull(IQ_fav.favorites,'') favorites, CAST(min(b.timestamp) as DATE) as 'First Search', CAST(max(b.timestamp) as DATE) as 'Last Search', CAST(MIN(c.timestamp) as DATE) as 'Registered'
  FROM [Fulfillment].[dbo].[bearfist_focus_log] b
  LEFT JOIN [Fulfillment].[dbo].[bearfist_focus_log] c ON b.email = c.email AND c.action = 'CONFIRM_REGISTER_FOCUS'
  LEFT JOIN  
  (
		--quicklook breakouts
		SELECT b.email,count(*) as quicklooks
		  FROM [Fulfillment].[dbo].[bearfist_focus_log] b
		  where b.quick_look_url IS NOT NULL  AND b.hg_product = 'HG Focus'
		  GROUP BY b.email
  ) as IQ on b.email = IQ.email
  LEFT JOIN  
  (
		--favorites breakouts
		SELECT b.email,count(*) as favorites
		  FROM [Fulfillment].[dbo].[bearfist_focus_log] b
		  where action = 'ADD_FAVORITE_PRODUCT' AND b.hg_product = 'HG Focus'
		  GROUP BY b.email
  ) as IQ_fav on b.email = IQ_fav.email
  WHERE b.domain = 'microsoft.com' AND b.action = 'GET_PRODUCTS' AND b.hg_product = 'HG Focus'
  AND b.timestamp >= '2018-01-1'
  GROUP BY b.email, b.domain, IQ.quicklooks, IQ_fav.favorites
  ORDER BY max(b.timestamp) DESC

--domain level stats
SELECT b.domain, count(*) as lookups, COUNT(DISTINCT b.email) as total_user_count, isnull(IQ_confirm.confirm_user_count,'') confirm_user_count
  FROM [Fulfillment].[dbo].[bearfist_focus_log] b
  LEFT JOIN 
  (
	  SELECT COUNT(*) as confirm_user_count, b.domain
	  FROM [Fulfillment].[dbo].[bearfist_focus_log] b
	  WHERE b.action = 'CONFIRM_REGISTER_FOCUS'
	  AND b.domain = 'microsoft.com' AND b.hg_product = 'HG Focus'
	  GROUP BY b.domain
  ) as IQ_confirm ON b.domain = IQ_confirm.domain
  where b.domain = 'microsoft.com' AND b.action = 'GET_PRODUCTS' AND b.hg_product = 'HG Focus'
  GROUP BY b.domain, IQ_confirm.confirm_user_count

--domain level stats by month
SELECT b.domain, year(b.timestamp) as year, month(b.timestamp) as month, count(*) as lookups, COUNT(DISTINCT b.email) as active_user_count, isnull(IQ_confirm.confirm_user_count,'') confirm_user_count, isnull(IQ_fav.favorites,'') as added_favorites
  FROM [Fulfillment].[dbo].[bearfist_focus_log] b
  LEFT JOIN 
  (
	  SELECT COUNT(*) as confirm_user_count, b.domain, year(b.timestamp) as year, month(b.timestamp) as month
	  FROM [Fulfillment].[dbo].[bearfist_focus_log] b
	  WHERE b.action = 'CONFIRM_REGISTER_FOCUS'
	  AND b.domain = 'microsoft.com' AND b.hg_product = 'HG Focus'
	  GROUP BY b.domain, year(b.timestamp), month(b.timestamp)
  ) as IQ_confirm ON b.domain = IQ_confirm.domain AND year(b.timestamp) = IQ_confirm.year AND month(b.timestamp) = IQ_confirm.month
  LEFT JOIN  
  (
		--favorites breakouts
		SELECT count(*) as favorites, b.domain, year(b.timestamp) as year, month(b.timestamp) as month
		  FROM [Fulfillment].[dbo].[bearfist_focus_log] b
		  where action = 'ADD_FAVORITE_PRODUCT' AND b.hg_product = 'HG Focus'
		  GROUP BY b.domain, year(b.timestamp), month(b.timestamp)
  ) as IQ_fav on b.domain = IQ_fav.domain AND year(b.timestamp) = IQ_fav.year AND month(b.timestamp) = IQ_fav.month
  where b.domain = 'microsoft.com' AND b.action = 'GET_PRODUCTS' AND b.hg_product = 'HG Focus'
  GROUP BY b.domain, year(b.timestamp), month(b.timestamp), IQ_confirm.confirm_user_count,IQ_fav.favorites
  order by year(b.timestamp) DESC, month(b.timestamp) desc

--what industry is being searched
SELECT top_level_industry, sub_level_industry, COUNT(*) as 'lookups', COUNT(Distinct email) as 'Unique Users'
FROM [Fulfillment].[dbo].[bearfist_focus_log] b
LEFT JOIN [Fulfillment].[dbo].[url] u ON b.url = u.url
LEFT JOIN [Fulfillment].[dbo].[industry] i ON u.industry_id = i.id
where b.action = 'GET_PRODUCTS' AND b.hg_product = 'HG Focus'
and domain = 'microsoft.com'
GROUP BY top_level_industry, sub_level_industry
order by count(*) desc

--what revenue is being searched
SELECT revenue_range, COUNT(*) as 'lookups', COUNT(Distinct email) as 'Unique Users'
FROM [Fulfillment].[dbo].[bearfist_focus_log] b
LEFT JOIN [Fulfillment].[dbo].[url] u ON b.url = u.url
LEFT JOIN [Fulfillment].[dbo].[revenue_range] r ON u.revenue_range_id = r.id
where b.action = 'GET_PRODUCTS' AND b.hg_product = 'HG Focus'
and domain = 'microsoft.com'
GROUP BY revenue_range, revenue_range_id
order by revenue_range_id

--what employee is being searched
SELECT employees_range, COUNT(*) as 'lookups', COUNT(Distinct email) as 'Unique Users'
Select top(100)* FROM [Fulfillment].[dbo].[bearfist_focus_log] b
LEFT JOIN [Fulfillment].[dbo].[url] u ON b.url = u.url
LEFT JOIN [Fulfillment].[dbo].[employees_range] r ON u.employees_range_id = r.id
where b.action = 'GET_PRODUCTS' AND b.hg_product = 'HG Focus'
and domain = 'microsoft.com'
GROUP BY employees_range, employees_range_id
order by employees_range_id
  
--quicklook breakouts (per user)
SELECT b.email, b.quick_look_url, count(*) as lookups, COUNT(DISTINCT b.email) as user_count, CAST(min(b.timestamp) as DATE) as 'First Search', CAST(max(b.timestamp) as DATE) as 'Last Search', CAST(MIN(c.timestamp) as DATE) as 'Registered'
  FROM [Fulfillment].[dbo].[bearfist_focus_log] b
  LEFT JOIN [Fulfillment].[dbo].[bearfist_focus_log] c ON b.email = c.email AND c.action = 'CONFIRM_REGISTER_FOCUS'
  where b.domain = 'microsoft.com' AND b.quick_look_url IS NOT NULL AND b.action = 'GET_PRODUCTS' AND b.hg_product = 'HG Focus'
  GROUP BY b.email, b.quick_look_url
  order by count(*) desc

--quicklooks breakouts (by domain)
SELECT b.domain, isnull(b.quick_look_url,'') quick_look_url, count(*) as lookups, COUNT(DISTINCT b.email) as user_count, CAST(min(b.timestamp) AS DATE) as 'First Search', CAST(max(b.timestamp) AS DATE) as 'Last Search'
  FROM [Fulfillment].[dbo].[bearfist_focus_log] b
  where b.domain = 'microsoft.com' AND b.hg_product = 'HG Focus'
  GROUP BY b.domain, b.quick_look_url
  order by count(*) desc

SELECT product, COUNT(*) as 'times favorited', COUNT(Distinct email) as 'Unique Users'
  FROM [Fulfillment].[dbo].[bearfist_focus_log]
  where action = 'ADD_FAVORITE_PRODUCT' AND hg_product = 'HG Focus'
  and domain = 'microsoft.com'
  GROUP BY product
  order by count(*) desc