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;

fork query

edited mar 27

created mar 27 anonymous