Data Mastery: SQL — Having There are so many exciting projects out there in the Data World. Artificial Intelligence, Machine Learning, Neural Nets, Blockchain, and more are sweeping the technology industry. In order to get to the cutting-edge stuff, first and foremost, data needs to be stored, evaluated, and tested. The best place to do that is SQL (or a library that operates with SQL-like commands, ). see my article on Python’s Pandas library This series , will teach you the essential subjects. These are not exhaustive tutorials. Instead they are focused preparation guides — with brevity and efficiency in mind. It is meant for: Data Mastery: SQL Software Engineers who want to analyze their creation’s data Product Managers, Marketers, and others who want to be data-driven Beginning Data Scientists, Data Engineers, Data Analysts, or Business Intelligence Developers preparing for interviews See my resource list of the books I used to prepare for my big interview Each article will contain a brief technical explanation of the subject, an example question, and an answer. Follow up articles will contain challenging questions so you can evaluate your learning. This series does not come with accompanying data sets. The advantage to this is when you are on the drawing board, whether in an interview or project design, you do not have test data to play with. You have to think abstract. Having One limitation of the WHERE clause is that you cannot use aggregations in its conditions. Here is our daily_user_score table from earlier in the series: date       | userid    | sessionid | score
------------------------------------------
2018–09–01 | 983489272 | 125       | 112
2018–09–01 | 234342423 | 34        | 112
2018–09–01 | 567584329 | 207       | 618
2018–09–02 | 983489272 | 126       | 410
2018–09–02 | 983489272 | 127       | 339 And here is a query that will not work: SELECT userid,
       COUNT(DISTINCT sessionid) AS num_sessions
FROM daily_user_score
WHERE COUNT(DISTINCT sessionid) > 2 -- DOESN'T WORK
GROUP BY userid; The query above is trying to find the number of sessions each user played if they played more than two games. This is possible in SQL just not in the WHERE clause. Instead we use the HAVING clause. The HAVING clause is very similar to the WHERE clause except that it goes after the GROUP BY statement. So the query instead will be: SELECT userid, 
       COUNT(DISTINCT sessionid) AS num_sessions
FROM daily_user_score
GROUP BY userid
HAVING COUNT(DISTINCT sessionid) > 2; This query returns: userid    | num_sessions
------------------------
983489272 | 3 Try it yourself Write a query that returns dates and count of unique users but only dates with more than one unique user. Answer SELECT date,
       COUNT(DISTINCT userid) as unique_users
FROM daily_user_score
GROUP BY date
HAVING COUNT(DISTINCT userid) > 1; This query returns: date       | unique_users
-------------------------
2018–09–01 | 3 Thanks for reading! If you have questions feel free to comment & I will try to get back to you. Connect with me on Instagram @lauren__glass Connect with me on LinkedIn Check out my essentials list on Amazon Search for me using my nametag on Instagram!

Amazon

Data Mastery: SQL — Having

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Data Mastery: SQL — Aggregation

Data Mastery: SQL — Aggregation

Data Mastery: SQL — Distinct

Data Mastery: SQL — Grouping

Data Mastery: SQL — Subqueries

Data Mastery: SQL — The Basic Query

Data Mastery: SQL — Aggregation

Data Mastery: SQL — Aggregation

Data Mastery: SQL — Distinct

Data Mastery: SQL — Grouping

Data Mastery: SQL — Subqueries

Data Mastery: SQL — The Basic Query

Light-Mode

Classic

Newspaper

Minty

Dark-Mode

Neon Noir

Minty

HN StartUps