0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for administrators and users of the CiviCRM Constituent Relationship Management software

--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, '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 others countries'
     end
from Users*/

--generte a frequency table for reputation--
/*select count (Id) as count , Reputation
from Users
group by Reputation 
having Reputation> 100 
order by Reputation desc */

--find users whos reputation are above average --
--first step: find acerage 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 User 
where Reputation > (select avg(Reputation)
                    from Users
                    where CreationDate > (select )
                    )*/


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

fork query

edited mar 27

created mar 27 anonymous