Practice

0

Please login or register to vote for this query.

(click on this box to dismiss)

Anime and Manga

Q&A for anime and manga fans

-- 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 Id
  FROM Posts
  WHERE OwnerUserId IN (SELECT Id 
                          FROM Users
                          WHERE CreationDate >= '2024-01-01')
  AND OwnerUserId IN (SELECT UserId
                        FROM Badges
                        WHERE lower(Name) LIKE '%gold%');

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results