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

-- get all the data from Users table -- 
-- SELECT * 
-- FROM Users 

-- Generate Freq. Dist Table for User Reputation -- 
/*SELECT count(Id) AS ReputationCount, Reputation 
FROM Users 
WHERE CreationDate > '2024-01-01 00:00:00' AND Reputation > 100  -- WHERE works on columns only, no aggregation functions-- 
GROUP BY Reputation -- x axis-- 
HAVING count(Id)>100 -- HAVING for aggregation functions --
ORDER BY ReputationCount desc
*/

-- GROUP BY -- 
-- SELECT Location, Sum(UpVotes) AS TotalUpvotes 
-- FROM Users
-- WHERE CreationDate > '2024-01-01 00:00:00'
-- GROUP BY Location

-- NESTED SQL--
-- Find Avg(Reputation)
-- SELECT AVG(Reputation) AS AvgReputation
-- FROM Users -- 95 --

-- Find Reputation Above Avg -- 

--SELECT Id, Reputation 
--FROM Users 
--WHERE Reputation > 95

/*SELECT Id, Reputation 
FROM Users
WHERE Reputation > (SELECT Avg(Reputation) FROM Users WHERE CreationDate > '2024-01-01 00:00:00')
and Location like '%Italy'
*/

--Users, Posts, Badges tables need to be joined--

SELECT DISTINCT(Class)
FROM Badges 
GROUP BY Class

fork query

edited mar 29

created mar 22 anonymous