Hello SQL

0

Please login or register to vote for this query.

(click on this box to dismiss)

Anime and Manga

Q&A for anime and manga fans

--select users coming from India China and USA -- 

-- after checking the logical model , the user table has all the info we need--
/*
select * 
from Users
where Location like '%China%' OR Location like '%India%' OR Location like '%USA%' 
*/

--rewrite by using case then --
/*
select Id, 'country coming from '=
case when Location like '%India%' then 'coming from India'
      when Location like '%China%' then 'coming from China'
      when Location like '%USA%' then 'coming from USA'
      when Location is null then 'no location info'
      else 'coming from other countries'
      
      end
from Users
*/


--generate a freq dist table for Reputation--
/*
select count(Id) as 'count',Reputation
from Users
group by Reputation
*/



/*
select count(Id) as 'countReputation',Reputation
from Users
group by Reputation
having Reputation > 100
order by Reputation desc
*/




-- find users whos reputation are above avg--
--first step : find avg reputation
/*
select avg(Reputation)
from Users

--second step : find the users who's reputation are above avg--

select ID
from Users 
where Reputation > 95

--rewrite in nest SQL--
select Id
from Users
where Reputation > (select avg(Reputation)
                    from Users
                    where CreationDate > (select )
                    )
*/


--find the posts the users who has gold badges and created their account in 2024
select *
from Posts
left join Users
on Posts.OwnerUserID = Users.Id
where Users.CreationDate > '2024-01-01 00:00:00'

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results