-- Interesting SQL:

---------------------------------------------------
-- Top N vote totals for a given category (NOT USEFUL):
SELECT count(weight) AS total, category_id, votes.gender, firstname, lastname
FROM votes, students ON voted_for = students.student_id
WHERE category_id = 6
AND votes.gender = 'M'
GROUP BY category_id, voted_for
ORDER BY category_id, votes.gender DESC, total DESC, lastname ASC
LIMIT 25;

---------------------------------------------------
-- Vote totals grouped by category and gender

SELECT DISTINCT category_id, votes.gender as vgender,  count(weight) AS total, firstname, lastname
FROM votes, students
WHERE voted_for = students.student_id
GROUP BY category_id, voted_for
ORDER BY category_id, vgender DESC, total DESC, lastname ASC;


---------------------------------------------------

-- failed
SELECT DISTINCT category_id as cid, votes.gender as vgender,  count(weight) AS total, firstname, lastname
FROM votes, students
WHERE voted_for = students.student_id
	AND category_id = 1
GROUP BY category_id, voted_for
HAVING total IN
	(SELECT top5, top5table.category_id, top5table.gender FROM (SELECT DISTINCT category_id, votes.gender, count(weight) AS top5
		FROM votes, students ON voted_for = students.student_id
		GROUP BY category_id, voted_for
		ORDER BY category_id, votes.gender DESC, top5 DESC, lastname ASC) as top5table
	--WHERE top5table.category_id = votes.category_id
	  --AND top5table.gender = votes.gender
	LIMIT 75)
ORDER BY category_id, vgender DESC, total DESC, lastname ASC;

-- top 5 for one category and one gender
SELECT DISTINCT category_id, students.gender, count(weight) AS top5
FROM votes, students ON voted_for = students.student_id
WHERE category_id = 6
	AND students.gender = 'M'
GROUP BY category_id, voted_for
ORDER BY category_id, votes.gender DESC, top5 DESC, lastname ASC
LIMIT 5;



-- Total # of votes needed to get rank 5 for one category/gender:
SELECT count(weight) AS cutoff--, category_id--, votes.gender, firstname, lastname
FROM votes, students ON voted_for = students.student_id
WHERE category_id = 6 AND votes.gender = 'M'
GROUP BY category_id, votes.gender, voted_for
ORDER BY category_id, votes.gender DESC, cutoff DESC, lastname ASC
LIMIT 25;



SELECT DISTINCT category_id as cid, votes.gender as vgender,  count(weight) AS total, firstname, lastname
FROM votes, students
WHERE voted_for = students.student_id
	AND category_id = 6
GROUP BY category_id, voted_for
ORDER BY category_id, vgender DESC, total DESC, lastname ASC;

---------------------------------------------------
-- Status: vote totals for 1 category


SELECT count(weight) AS total, firstname, lastname
FROM votes, students
	ON voted_for = students.student_id
WHERE	
	category_id = 1
	AND votes.gender = 'M'
GROUP BY voted_for
HAVING total > 1
ORDER BY total DESC, lastname ASC;

-- equivalent to 

SELECT total, firstname, lastname FROM
	(SELECT count(weight) as total, voted_for FROM superlatives_votes
		WHERE category_id = :c AND gender = :g
		GROUP BY voted_for) AS v
CROSS JOIN students ON v.voted_for = students.student_id
ORDER BY total DESC, lastname ASC;


---------------------------------------------------

-- Tracks votes for Dani
SELECT firstname, lastname, description
FROM votes, superlatives_categories as categories, students,
 (select student_id from students where firstname = "Danielle" and lastname = "Meier") as dani
WHERE votes.category_id = categories.category_id
AND votes.voted_for = dani.student_id
AND votes.student_id = students.student_id
ORDER BY lastname, description;

---------------------------------------------------

-- Voters and how many people they voted for
sELECT count(weight) as total, firstname, lastname FROM votes, students
WHERE votes.student_id = students.student_id
GROUP BY votes.student_id ORDER BY total DESC;

-- Likewise: nonvoters (12th-graders only)
SELECT firstname, lastname FROM students	WHERE grade = 12
EXCEPT SELECT firstname, lastname FROM votes, students
	WHERE votes.student_id = students.student_id
GROUP BY votes.student_id ORDER BY lastname ASC;