Skip to content

Instantly share code, notes, and snippets.

@billymoon
Created March 13, 2012 17:17
Show Gist options
  • Save billymoon/2029991 to your computer and use it in GitHub Desktop.
Save billymoon/2029991 to your computer and use it in GitHub Desktop.
CREATE TABLE IF NOT EXISTS `purchases` (`Purchase_ID` int(11), `Datetime` datetime, `Reward_ID` int(11), `Quantity` int(11), `Student_ID` int(11), `Student_Name` varchar(100), `Date_DealtWith` datetime, `Date_Collected` datetime);
DELETE FROM `purchases`;
INSERT INTO `purchases` (`Purchase_ID`, `Datetime`, `Reward_ID`, `Quantity`, `Student_ID`, `Student_Name`, `Date_DealtWith`, `Date_Collected`) VALUES
(1, '2011-09-27 16:55:16', 1, 1, 34240, '', '2011-09-27 16:55:16', '2011-12-12 15:45:43'),
(2, '2011-09-28 13:02:26', 1, 1, 137636, '', '2011-09-27 16:55:16', '2011-09-27 16:55:16'),
(3, '2011-09-29 11:29:09', 1, 1, 137685, '', NULL, NULL),
(4, '2011-09-29 11:29:09', 1, 1, 928734, '', NULL, NULL);
SELECT * FROM `purchases`;
CREATE TABLE IF NOT EXISTS `transactions` (`Transaction_ID` int(11), `Datetime` datetime, `Giver_ID` int(11), `Recipient_ID` int(11), `Points` int(11), `Category_ID` int(11), `Reason` varchar(100));
DELETE FROM `transactions`;
INSERT INTO `transactions` (`Transaction_ID`, `Datetime`, `Giver_ID`, `Recipient_ID`, `Points`, `Category_ID`, `Reason`) VALUES
(1, '2011-09-07', 36754, 34401, 5, 6, 'Gave excellent feedback on the new student notebook'),
(2, '2011-09-07', 34972, 137615, 10, 9, 'Helping TG'),
(6, '2011-09-07', 35006, 90185, 2, 1, '');
SELECT * FROM `transactions`;
SELECT
numrewards_students,
numpurchase_students,
numpurchase_students / numrewards_students * 100.0
FROM
(SELECT 0 AS joiner, COUNT(DISTINCT Recipient_ID) AS numrewards_students FROM transactions) AS trs JOIN
(SELECT 0 AS joiner, COUNT(DISTINCT Student_ID) AS numpurchase_students FROM purchases) AS prs ON trs.joiner = prs.joiner;
SELECT
numrewards_students,
numpurchase_students,
numpurchase_students / numrewards_students * 100.0
FROM
(
(SELECT COUNT(DISTINCT Recipient_ID) AS numrewards_students FROM transactions) AS numrewards_students,
(SELECT COUNT(DISTINCT Student_ID) AS numpurchase_students FROM purchases) AS numpurchase_students
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment