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