High SQL Queries for Information Scientists

Picture by Creator

I do know the phrase ‘Python’ might be essentially the most overused phrase within the context of knowledge science. To a point, there’s a purpose for that. However, on this article, I need to deal with SQL, which frequently will get neglected when speaking about knowledge science. I emphasize speaking as a result of, in follow, SQL is just not neglected in any respect. Quite the opposite, it’s one of many holy trinity of the programming languages in knowledge science: SQL, Python, and R.

SQL is made for knowledge querying and manipulation but additionally has respectable knowledge evaluation and reporting capabilities. I’ll present a number of the important SQL ideas you want as an information scientist and a few simple examples from StrataScratch and LeetCode.

Then, I’ll present two widespread enterprise eventualities wherein all or most of these SQL ideas should be utilized.

Primary SQL Ideas for Information Scientists

Right here’s the overview of the ideas I’ll focus on.

1. Querying and Filtering Information

That is the place your sensible work as an information scientist often begins: querying a database and extracting solely the information you want on your process.

This usually includes comparatively easy SELECT statements with the FROM and WHERE clauses. To get the distinctive values, use DISTINCT. If you could use a number of tables, you additionally add JOINs.

You’ll usually want to make use of ORDER BY to make your dataset extra organized.

Instance of Combining Two Tables: You may be required to listing the individuals’ names and town and state they stay in by becoming a member of two tables and sorting the output by final title.

SELECT FirstName,
       LastName, 
       Metropolis, 
       State
FROM Individual p LEFT JOIN Tackle a
ON p.PersonId = a.PersonId
ORDER BY LastName ASC;

2. Working with NULLs

NULLs are values that knowledge scientists are sometimes not detached to – they both need solely NULLs, they need to take away them, or they need to exchange them with one thing else.

You may choose knowledge with or with out NULLs utilizing IS NULL or IS NOT NULL in WHERE.

Changing NULLs with another values is usually performed utilizing conditional expressions:

• NULLIF()
• COALESCE()
• CASE assertion 

Instance of IS NULL: With this question, you could find all the shoppers not referred by the shopper with ID = 2.

SELECT title 
FROM buyer 
WHERE referee_id IS NULL OR referee_id <> 2;

Instance of COALESCE(): I can rework this instance by saying I need to question all the information but additionally add a column that may present 0% as a bunch response fee as a substitute of NULL.

SELECT *,
       COALESCE(host_response_rate, '0%') AS edited_host_response_rate
FROM airbnb_search_details;

3. Information Kind Conversion 

As an information scientist, you’ll convert knowledge regularly. Information usually doesn’t come within the desired format, so you will need to adapt it to your wants. That is often performed utilizing CAST(), however there are additionally some alternate options, relying in your SQL taste.

Instance of Casting Information: This question casts the star knowledge from VARCHAR to INTEGER and removes the values which have non-integer values.

SELECT business_name,
       review_id,
       user_id,
       CAST(stars AS INTEGER) AS cast_stars,
       review_date,
       review_text,
       humorous,
       helpful,
       cool
FROM yelp_reviews
WHERE stars  '?';

4. Information Aggregation

To raised perceive the information they’re working with (or just because they should produce some experiences), knowledge scientists fairly often need to combination knowledge.

Typically, you will need to use combination capabilities and GROUP BY. A few of the widespread combination capabilities are:

• COUNT()
• SUM()
• AVG()
• MIN()
• MAX()

If you wish to filter aggregated knowledge, use HAVING as a substitute of WHERE.

Instance of Sum: You should use this question to sum the checking account for every person and present solely these with a stability above 1,000.

SELECT u.title, 
       SUM(t.quantity) AS stability
FROM Customers u
JOIN Transactions t
ON u.account = t.account
GROUP BY u.title
HAVING SUM(t.quantity) > 10000;

5. Dealing with Dates

Working with dates is commonplace for knowledge scientists. Once more, the dates are solely generally formatted in response to your style or wants. To maximise the pliability of dates, you’ll generally have to extract elements of dates or reformat them. To try this in PostgreSQL, you’ll mostly use these date/time capabilities:

• EXTRACT()
• DATE_PART()
• DATE_TRUNC()
• TO_CHAR() 

One of many widespread operations with dates is to discover a distinction between the dates or so as to add dates. You do this by merely subtracting or including the 2 values or through the use of the capabilities devoted for that, relying on the database you employ.

Instance of Extracting 12 months: The next question extracts the 12 months from the DATETIME sort column to indicate the variety of violations per 12 months for Roxanne Cafe.

SELECT EXTRACT(YEAR FROM inspection_date) AS year_of_violation,
       COUNT(*) AS n_violations
FROM sf_restaurant_health_violations
WHERE business_name="Roxanne Cafe" AND violation_id IS NOT NULL
GROUP BY year_of_violation
ORDER BY year_of_violation ASC;

Instance of Date Formatting: With the question beneath, you format the beginning date as ‘YYYY-MM’ utilizing TO_CHAR().

SELECT TO_CHAR(started_at, 'YYYY-MM'),
       COUNT(*) AS n_registrations
FROM noom_signups
GROUP BY 1;

6. Dealing with Textual content

Other than dates and numerical knowledge, fairly often databases comprise textual content values. Generally, these values need to be cleaned, reformatted, unified, break up and merged. Attributable to these wants, each database has many textual content capabilities. In PostgreSQL, a number of the extra fashionable ones are:

• CONCAT() or ||
• SUBSTRING()
• LENGTH()
• REPLACE()
• TRIM()
• POSITION()
• UPPER() & LOWER()
• REGEXP_REPLACE() & REGEXP_MATCHES() & REGEXP_SPLIT_TO_ARRAY()
• LEFT() & RIGHT()
• LTRIM() & RTRIM()

There are often some overlapping string capabilities in all databases, however every has some distinct capabilities.

Instance of Discovering the Size of the Textual content: This question makes use of the LENGTH() operate to seek out invalid tweets based mostly on their size.

SELECT tweet_id 
FROM Tweets 
WHERE LENGTH(content material) > 15;

7. Rating Information

Rating knowledge is among the widespread duties in knowledge science. As an illustration, it may be used to seek out one of the best or worst-selling merchandise, quarters with the very best income, songs ranked by variety of streams, and the very best and lowest-paid workers.

The rating is finished utilizing window capabilities (which we’ll speak a bit extra within the subsequent part):

• ROW_NUMBER()
• RANK()
• DENSE_RANK()

Instance of Rating: This question makes use of DENSE_RANK() to rank hosts based mostly on the variety of beds they’ve listed.

SELECT host_id, 
       SUM(n_beds) AS number_of_beds,
       DENSE_RANK() OVER(ORDER BY SUM(n_beds) DESC) AS rank
FROM airbnb_apartments
GROUP BY host_id
ORDER BY number_of_beds DESC;

8. Window Capabilities

Window capabilities in SQL help you calculate the rows associated to the present row. This attribute is just not solely used to rank knowledge. Relying on the window operate class, they’ll have many alternative makes use of. You may learn extra about them within the window capabilities article. Nevertheless, their important attribute is that they’ll present analytical and aggregated knowledge on the identical time. In different phrases, they don’t collapse particular person rows when performing calculations.

Instance of FIRST_VALUE() Window Operate: One window operate instance is to indicate the most recent person login for a selected 12 months. The FIRST_VALUE() window operate makes this simpler.

SELECT DISTINCT user_id,
       FIRST_VALUE(time_stamp) OVER (PARTITION BY user_id ORDER BY time_stamp DESC) AS last_stamp
FROM Logins
WHERE EXTRACT(YEAR FROM time_stamp) = 2020;

9. Subqueries & CTEs

Subqueries and CTEs (often called tidier subqueries) help you attain a extra superior stage of calculations. By realizing subqueries and CTEs, you’ll be able to write complicated SQL queries, with subqueries or CTEs used for sub-calculations referenced in the primary question.

Instance of Subqueries and CTEs: The question beneath makes use of the subquery to seek out the primary 12 months of the product sale. This knowledge is then utilized in WHERE for the primary question to filter knowledge.

SELECT product_id, 
       12 months AS first_year, 
       amount, 
       worth 
FROM Gross sales 
WHERE (product_id, 12 months) IN (
    SELECT product_id, 
           MIN(12 months) AS 12 months 
    FROM Gross sales 
    GROUP BY product_id
);

The code might be written utilizing CTE as a substitute of a subquery.

WITH first_year_sales AS (
    SELECT product_id, 
           MIN(12 months) AS first_year 
    FROM Gross sales 
    GROUP BY product_id
)

SELECT s.product_id, 
       s.12 months AS first_year, 
       s.amount, 
       s.worth 
FROM Gross sales s
JOIN first_year_sales AS fys 
ON s.product_id = fys.product_id AND s.12 months = fys.first_year;

Enterprise Examples of Utilizing SQL

Let’s now have a look at a few enterprise circumstances the place knowledge scientists can use SQL and apply all (or most) of the ideas we mentioned earlier.

Discovering Greatest Promoting Product

On this instance, you will need to know subqueries, knowledge aggregation, dealing with dates, rating knowledge utilizing window capabilities, and filtering the output.

The subquery calculates every product’s gross sales for every month and ranks them by gross sales. The principle question then merely selects the required columns and leaves solely merchandise with the primary rank, i.e., best-selling merchandise.

SELECT sale_month,
       description,
       total_paid
FROM
  (SELECT DATE_PART('MONTH', invoicedate) AS sale_month,
          description,
          SUM(unitprice * amount) AS total_paid,
          RANK() OVER (PARTITION BY DATE_PART('MONTH', invoicedate) ORDER BY SUM(unitprice * amount) DESC) AS sale_rank
   FROM online_retail
   GROUP BY sale_month,
            description) AS ranking_sales
WHERE sale_rank = 1;

Calculating Shifting Common

The rolling or shifting common is a typical enterprise calculation to which knowledge scientists can apply their intensive SQL information, as in this instance.

The subquery within the code beneath calculates revenues by month. The principle question then makes use of the AVG() window capabilities to calculate the 3-month rolling common income.

SELECT t.month,
       AVG(t.monthly_revenue) OVER(ORDER BY t.month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_revenue
FROM
  (SELECT TO_CHAR(created_at::DATE, 'YYYY-MM') AS month,
          SUM(purchase_amt) AS monthly_revenue
   FROM amazon_purchases
   WHERE purchase_amt>0
   GROUP BY 1
   ORDER BY 1) AS t
ORDER BY t.month ASC;

Conclusion

All these SQL queries present you easy methods to use SQL in your knowledge science duties. Whereas SQL is just not made for complicated statistical evaluation or machine studying, it’s excellent for querying, manipulating, aggregating knowledge, and performing calculations.

These instance queries ought to enable you in your job. Should you don’t have an information science job, many of those queries will come up in your SQL interview questions.

Nate Rosidi is an information scientist and in product technique. He is additionally an adjunct professor educating analytics, and is the founding father of StrataScratch, a platform serving to knowledge scientists put together for his or her interviews with actual interview questions from prime corporations. Nate writes on the most recent tendencies within the profession market, offers interview recommendation, shares knowledge science initiatives, and covers every part SQL.