Hello SQL

0

Please login or register to vote for this query.

(click on this box to dismiss)

Craft CMS Meta

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 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 counties'
      end 
from Users
*/

--generte 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 --
-- first step: find average reputation
/*select avg(Reputation)
from Users
-- second step: find the users who's reputation are above average--
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 )
                   )
                   
                   
,
     User_created_in2024 (Id) as (
                                  select Id
                                  from Users
                                  where CreationDate > '2024-01-01 00:00:00'
                                  )

*/


-- find the posts the user who has gold badges and created their account in year 2024 --
--rewirte by using with --
--with gold_badges_users (Id) as (
                         select *
                         from Badges 
                         inner join Users
                         on Badges.UserId = Users.Id
                         where Class = 1
--                         )

--select Id
--from gold_badges_users

Enter Parameters

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