DataLemur Solutions

--> Top Rated Businesses:
https://datalemur.com/questions/sql-top-businesses

SELECT 
  COUNT(review_stars) AS business_count,
  ROUND(100.0 * COUNT(business_id)/
    (SELECT COUNT (business_id) FROM reviews),0) AS top_rated_pct
FROM reviews
WHERE review_stars IN (4, 5);

--> App Click-through Rate (CTR)
https://datalemur.com/questions/sql-app-ctr
with cte as
  (select app_id, 
    sum(case when event_type='impression' then 1 else 0 end) as no_of_impressions,
    sum(case when event_type='click' then 1 else 0 end) as no_of_clicks 
    from events
    where timestamp >= '2022-01-01' and timestamp < '2023-01-01'
    group by app_id)
select app_id, round(100.0 * no_of_clicks / no_of_impressions,2) as ctr from cte;


https://datalemur.com/questions/second-day-confirmation:

SELECT DISTINCT user_id
FROM emails 
INNER JOIN texts
  ON emails.email_id = texts.email_id
WHERE texts.action_date = emails.signup_date + INTERVAL '1 day'
  AND texts.signup_action = 'Confirmed';




https://datalemur.com/questions/time-spent-snaps

with cte as 
(select ag.age_bucket,
  sum(case when ac.activity_type = 'open' then ac.time_spent else 0 end) as s_open,
  sum(case when ac.activity_type = 'send' then ac.time_spent else 0 end) as s_send,
  sum(ac.time_spent) AS total_timespent
from activities ac inner join
age_breakdown ag on ac.user_id = ag.user_id
where ac.activity_type in ('open','send')
group by ag.age_bucket)
select age_bucket,
round(100.0*s_send/total_timespent,2) as send_perc,
round(100.0*s_open/total_timespent,2) as open_perc
from cte


https://datalemur.com/questions/rolling-average-tweets
SELECT    
  user_id,    
  tweet_date,   
  ROUND(AVG(tweet_count) OVER (
    PARTITION BY user_id     
    ORDER BY tweet_date     
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
  ,2) AS rolling_avg_3d
FROM tweets;



https://datalemur.com/questions/sql-highest-grossing
with cte as
(
select category,product,sum(spend) as total_spend
from product_spend 
        where transaction_date >= '2022-01-01' 
        and transaction_date <= '2022-12-31' 
        group by category,product
),
cte2 as 
(
select *,rank() over(partition by category order by total_spend desc) as rn
from cte
)
select category,product,total_spend 
from cte2 where rn <= 2 
order by category, rn;


https://datalemur.com/questions/top-fans-rank

with cte as 
(
select a.artist_id, a.artist_name, s.song_id,s.artist_id,
gs.song_id,gs.rank
from artists a 
inner join songs s on a.artist_id = s.artist_id
inner join global_song_rank gs on s.song_id = gs.song_id
),
cte2 as 
(
select artist_name,count(rank)  as cnt from cte where rank <=10
group by artist_name
),
cte3 as 
(select artist_name,dense_rank() over(order by cnt desc)  as artist_rank from cte2)
select * from cte3 where artist_rank <=5