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 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'
      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--
-- 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 ))

*/



-- find the posts the user who has gold badges and created their account in--
/*with god_badges_users (ID) as (
                         select Id
                         from Badges
                         inner join Users
                         on Badges.UserId = Users.Id
                         ),
    User_created_in2024 (Id) as (
                                 select Id
                                 from Users
                                 where CreationDate > '2024-01-01 00:00:00'
                                 )
                                 
                                 
select Id 
from gold_badges_users
                                 
select*
from Posts
full join Users
on Posts.OwnerUserId = User.Id
where Users.CreationDate > '2024-01-01 00:00:00'
and
Badges.Class = 1
*/

/*2: How many users didn’t post any questions in year2015.(hint: use PostType table to check which post types is question)

3: How many post titles related to ‘sports’ without any accepted answer?

4: The user who created the most viewed posts in year 2023. What kind of badges does he/she own?

5: How many posts are voted as spam?
*/

SELECT COUNT(*) AS UsersWithoutQuestions
FROM Users u
WHERE NOT EXISTS (
    SELECT 1
    FROM Posts p
    INNER JOIN PostTypes pt ON p.PostTypeId = pt.Id
    WHERE pt.Name = 'Question'
    AND YEAR(p.CreationDate) = 2015
    AND p.OwnerUserId = u.Id
);

fork query

edited mar 27

created mar 27 anonymous