0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for administrators, end users, developers and designers for Craft CMS

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

--rewrite by using case then --
/*select Id, Location, '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 information'
      else 'coming from other countries' 
      end
from Users*/
--generate a frequency table for 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 average--
select avg(Reputation)
from Users
--second step: find the users who's reputation are above average--
select Id
from Users
where Reputation>95

fork query

edited mar 27

created mar 27 anonymous