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 US
-- the user table has all the information we need

/*SELECT *
  FROM Users
  WHERE lower(Location) LIKE '%india%' 
  OR lower(Location) LIKE '%china%'
  OR lower(Location) LIKE '%usa%';
  */

-- rewrite using CASE THEN
/*SELECT Id, Location, CASE 
    WHEN lower(Location) LIKE '%india%' THEN 'coming from Inida'
    WHEN lower(Location) LIKE '%china%' THEN 'coming from China'
    WHEN lower(Location) LIKE '%usa%' THEN 'coming from USA'
    WHEN Location IS NULL THEN 'no location information'
    ELSE 'coming from other countries'
    END AS 'country coming from'
 FROM Users;
 */
 
-- generate a frequency table for reputation
/*SELECT COUNT(Id) AS 'Counts', Reputation
  FROM Users
  GROUP BY Reputation
  HAVING COUNT(Id) > 100 AND COUNT(Id) < 200
  ORDER BY 2 DESC;
  */

-- find the users who's reputation is above average 
/*SELECT Id
  FROM Users
  WHERE Reputation > (SELECT AVG(Reputation)
                        FROM Users);
*/

-- find gold badge users posts who created their accounts in year 2024
/*SELECT *
  FROM Posts
  WHERE OwnerUserId IN (SELECT Id 
                          FROM Users
                          WHERE CreationDate >= '2024-01-01')
  AND OwnerUserId IN (SELECT UserId
                        FROM Badges
                        WHERE Class = 1);
*/

-- rewrite using with function
/*with gold_badges_users(Id) AS (SELECT UserId
                           FROM Badges b
                           INNER JOIN Users u
                           on b.UserId = u.Id
                           WHERE Class=1),
     user_created_in2024(Id) AS (SELECT Id 
                                   FROM Users
                                   WHERE CreationDate >= '2024-01-01')

SELECT *
  FROM gold_badges_users;

SELECT *
  FROM Badges;
*/




/*SELECT COUNT(Id)
  FROM Users
  WHERE WebsiteUrl IS NULL
  AND Location IS NULL;
*/

/*SELECT COUNT(Title)
  FROM Posts
  WHERE lower(Title) LIKE '%sports%'
  AND AcceptedAnswerId IS NULL;
*/

/*SELECT COUNT(OwnerUserId)
  FROM Posts
  WHERE CreationDate BETWEEN '2015-01-01' AND '2015-12-31'
  AND PostTypeId IN 
                  (SELECT Id
                     FROM PostTypes
                     WHERE Name NOT LIKE '%question%');
*/

SELECT TOP 1 *
  FROM VoteTypes;

fork query

edited mar 27

created mar 27 anonymous