earned_badges.sql

earned_badges.sql

/*
Earned Badges
This table contains badges earned by a user, including the most recent date achieved, the type, the name, the # of energy points earned, and the activity earned from.
Collected by: https://www.khanacademy.org/profile/chopsor/
*/

CREATE TABLE badges (
    date TEXT,
    badge_type TEXT,
    badge_name TEXT,
    energy_points INTEGER
);

INSERT INTO badges (date, badge_type, badge_name, energy_points) VALUES ("2015, 04/12", "Sun", "Oracle", 0);
INSERT INTO badges (date, badge_type, badge_name, energy_points) VALUES ("2015, 04/28", "Earth", "Incredible Inspiration", 5000);
INSERT INTO badges (date, badge_type, badge_name, energy_points) VALUES ("2015, 01/29", "Challenge Patch", "Intro to JS: Drawing & Animation Mastery", 0);
INSERT INTO badges (date, badge_type, badge_name, energy_points) VALUES ("2014, 12/05", "Meteorite", "Thumbs Up", 0);
INSERT INTO badges (date, badge_type, badge_name, energy_points) VALUES ("2015, 04/28", "Moon", "1000 Kelvin", 1000);
INSERT INTO badges (date, badge_type, badge_name, energy_points) VALUES ("2015, 04/25", "Earth", "299,792,458 Meters per Second", 5000);
INSERT INTO badges (date, badge_type, badge_name, energy_points) VALUES ("2015, 03/20", "Sun", "Da Vinci", 200000);
INSERT INTO badges (date, badge_type, badge_name, energy_points) VALUES ("2015, 03/02", "Sun", "Newton", 150000);
INSERT INTO badges (date, badge_type, badge_name, energy_points) VALUES ("2015, 03/02", "Sun", "Hypatia", 125000);
INSERT INTO badges (date, badge_type, badge_name, energy_points) VALUES ("2015, 02/24", "Sun", "Kepler", 125000);
INSERT INTO badges (date, badge_type, badge_name, energy_points) VALUES ("2015, 02/24", "Sun", "Copernicus", 80000);
INSERT INTO badges (date, badge_type, badge_name, energy_points) VALUES ("2015, 02/07", "Sun", "Sally Ride", 35000);
INSERT INTO badges (date, badge_type, badge_name, energy_points) VALUES ("2015, 01/06", "Sun", "Magellan", 30000);
INSERT INTO badges (date, badge_type, badge_name, energy_points) VALUES ("2015, 02/24", "Earth", "Guru", 0);
INSERT INTO badges (date, badge_type, badge_name, energy_points) VALUES ("2014, 12/29", "Earth", "Work Horse", 14000);
INSERT INTO badges (date, badge_type, badge_name, energy_points) VALUES ("2014, 10/20", "Moon", "Redwood", 0);
INSERT INTO badges (date, badge_type, badge_name, energy_points) VALUES ("2013, 10/20", "Meteorite", "Cypress", 0);
INSERT INTO badges (date, badge_type, badge_name, energy_points) VALUES ("2015, 03/18", "Sun", "Millionaire", 0);
INSERT INTO badges (date, badge_type, badge_name, energy_points) VALUES ("2015, 02/27", "Earth", "Five Times Ten to the Fifth", 0);
INSERT INTO badges (date, badge_type, badge_name, energy_points) VALUES ("2015, 04/04", "Earth", "Investigator", 0);

## My data dig project
-- view data 
SELECT * FROM badges
LIMIT 2;

-- What are average, max, and min values in the data?
-- average,max and min of values in data
SELECT AVG(energy_points) AS "avg_en_points",MAX(energy_points) AS "max_en_points",MIN(energy_points) AS "min_en_points"
FROM badges;


-- What about those numbers per category in the data (using HAVING)?

-- got a badge when average & max
-- where greater than 10000

SELECT date,badge_name,badge_type,
ROUND(AVG(energy_points),2) AS 
"avg_en_points", 
MAX(energy_points) AS "max_en_points" FROM badges
    GROUP BY date
    HAVING avg_en_points > 10000 AND 
    max_en_points > 10000
    ;
--What ways are there to group the data --values that don’t exist yet 
--(using CASE)?

-- aim for targets greater than 15000
-- Or Within 10000 - 15000
-- the student is rather hardworking
SELECT date,badge_name, badge_type,
    CASE
        WHEN energy_points > 10000-15000 THEN "Within target"
        WHEN energy_points < 10000 THEN "almost there"
        WHEN energy_points > 15000 THEN "above target"
        ELSE "below target"
    END as "target"
FROM badges
GROUP BY target;

--What interesting ways are there to --filter the data (using AND/OR)?
-- where the learner got about 11000
-- OR 15000 and count occurences

SELECT badge_type,date, 
MIN (energy_points) = 11000 OR 15000
AND COUNT(*)
FROM badges
GROUP BY badge_type;

i added my code which i used to complete the Data dig project on Khan Academy. Hope it gives more insights. Thanks again for posting it here.