Image by Author | Canva

Data analysts commonly use SQL when creating various reports and calculating business metrics. In this article, we’ll show you several queries that will help you in everyday data analysis tasks. The examples are SQL query interview questions from StrataScratch.

Here’s an overview of examples and SQL concepts we’ll use.

Time Binning With DATE_TRUNC()

Timestamps is a common data type you’ll work with. Unfortunately, time periods are not inherent in timestamps. This makes it difficult to analyze trends without first manipulating the data. However, with DATE_TRUNC(), we can perform time binning, i.e., grouping timestamps into a specific time period.

Example: We can create a weekly orders report with these requirements. In the code below, we categorize the data from the week column into weeks using DATE_TRUNC(). In addition, we also use EXTRACT() to find only orders from the first quarter of 2023.

Then, the result of the CTE is called in SELECT, and we use SUM() to calculate the total quantity.

WITH filtered_orders AS (
    SELECT DATE_TRUNC('week', week::DATE) AS week_start,
           quantity 
    FROM orders_analysis
    WHERE EXTRACT(QUARTER FROM week::DATE) = 1 AND EXTRACT(YEAR FROM week::DATE) = 2023
)

SELECT week_start AS week, 
       SUM(quantity) AS total_quantity
FROM filtered_orders
GROUP BY week_start
ORDER BY week_start;

Here’s the output snapshot. You can see that the dates start from 2023-01-02, which was the first Monday (i.e., the start of the week) of 2023. As the rows represent the weeks, the difference between the dates is 7 days.

Where You Can Use It:

• Weekly or monthly growth trends
• Quarterly revenue changes
• Daily churn and usage drops

Calculating Rates (Conversion, Completion, Activation, Retention, and More)

Calculating rates is helpful when quantifying user behavior over time. They help capture patterns like how frequently users return, how quickly they convert, or how likely they are to churn.

Example: The code below calculates the first-day retention rate of a group of video game players.

The CTE identifies the first login.

The SELECT statement then counts the number of players who logged in the day after the first day (WHERE login_date – first_day = 1) and divides the count of those players by the total count of players to get the rate.

WITH first_days AS
  (SELECT player_id,
          MIN(login_date) AS first_day
   FROM players_logins
   GROUP BY player_id)
   
SELECT CAST(COUNT(DISTINCT first_days.player_id) AS FLOAT)/
  (SELECT COUNT(DISTINCT player_id) AS total_users
   FROM players_logins) AS retention_rate
FROM first_days
JOIN players_logins ON first_days.player_id = players_logins.player_id
WHERE login_date - first_day = 1;

Here’s the output.

Where You Can Use It:

• Growth dashboards with D1, D3, D7, and D30 retention rates (with just slightly changing the logic, e.g., login_date – first_day = 7)
• A/B tests on onboarding flows or welcome screens
• Cohort reports
• Funnel metrics
• Marketing performance (e.g., click-through rates, cost-per-click)
• Sales analytics
• Customer support (e.g., resolution rate, first-contact resolution rate)
• User engagement
• Product launch

Categorical Breakdowns With Aggregate Functions

As a data analyst, you’ll very often have to perform categorical breakdowns to help you understand the distribution of data.

Example: The code calculates the average number of bathrooms and bedrooms for each city’s property types, utilizing the AVG() aggregate function.

In addition, there are WHERE and HAVING to filter data according to requirements before and after aggregation.

SELECT city,
       property_type,
       AVG(bathrooms) AS n_bathrooms_avg,
       AVG(bedrooms) AS n_bedrooms_avg
FROM airbnb_search_details
GROUP BY city, property_type;

Here’s the partial output.

Where You Can Use It:

• Product
• Users
• Content
• Orders
• Headcounts breakdowns

Identifying Top and Bottom Performers With Window Functions

Data analysts are commonly asked to deliver a report of the top-n or bottom-n of a certain category, i.e., best and worst performers/cases.. In SQL, this is typically done by using the ranking window functions such as RANK() or DENSE_RANK().

Example: Here we write a query that identifies the bottom two companies by mobile usage.

We use COUNT(*) to count the number of mobile events, then DENSE_RANK() to rank them by event count. The ranking is in ascending order, meaning the lowest number of events will be ranked as 1. The DENSE_RANK() window function will show duplicate ranks without skipping between the ranks. We need that because skipping ranks can potentially mean we miss some users. If you wanted to skip ranks (in other scenarios), then you’d use RANK().

All this is written as a CTE. In SELECT, we leave only the users where the rank is 2 or lower (i.e., 1), which will show the bottom two users.

WITH ranked_companies AS
  (SELECT customer_id,
          COUNT(*) AS events,
          DENSE_RANK() OVER (ORDER BY COUNT(*)) AS rnk
   FROM fact_events
   WHERE client_id = 'mobile'
   GROUP BY customer_id)
   
SELECT customer_id,
       events
FROM ranked_companies
WHERE rnk <= 2
ORDER BY events ASC;

The output shows three users.

Where You Can Use It:

• Top-selling/worst-selling products
• Highest/lowest paid employees
• Students with the highest/lowest grades
• Longest/shortest retained customers
• Highest/lowest costs

Handling NULLs With COALESCE()

The data you’ll work with is messy, often with some data missing. You need to handle those gaps so your metrics aren’t messed up with NULL values, in which case you have to use COALESCE().

Example: In this example, we’ll calculate the total listening time and the count of unique songs the users have listened to.

In the query, we sum and round the listening duration, then divide by 60 to convert it to seconds. In addition, we use COALESCE() to replace all NULL values with 0; this way, we prevent incorrect results caused by missing data.

We also use COUNT() and DISTINCT to calculate the total number of unique songs each user has listened to.

SELECT user_id, 
       ROUND(SUM(COALESCE(listen_duration, 0)) / 60.0) AS total_listen_duration, 
       COUNT(DISTINCT song_id) AS unique_song_count
FROM listening_habits
GROUP BY user_id;

Here’s the partial output.

Where You Can Use It:

• Contact info cleanup
• Missing transaction amounts
• For missing dates
• For missing categorical labels

Calculating Running (Cumulative) Totals

Cumulative or running totals is a metric that data analysts calculate often. In SQL, it’s performed using the SUM() window function.

Example: In this example, we will calculate the cumulative total not as a final output, but as a step to it. The goal is to find the minimum number of platforms necessary to accommodate the entire scheduled traffic.

In the CTE, we write two SELECT statements with UNION ALL. The first one finds the train IDs and their arrival times, which are marked as 1. The second SELECT does the same for departure times, which are marked as -1.

The next CTE calculates the cumulative sum of mark values, which gives us the total number of trains present at the station. This is done by using the SUM() window function on the time and mark values descendingly, which cumulates the number of trains from the earliest to the latest time in a day.

In the final SELECT, we apply the MAX() function to the cumulative value to output the minimum number of platforms needed, as all trains that are present at the same time at the train station require, obviously, a platform each.

WITH timetable AS(
    (SELECT train_id,
            arrival_time AS TIME,
            1 AS mark
     FROM train_arrivals
     
     UNION ALL 
     
     SELECT train_id,
            departure_time AS TIME,
            -1 AS mark
     FROM train_departures
     )
    ORDER BY time, mark DESC),
    
cumsum AS
  (SELECT *,
          SUM(mark) OVER (ORDER BY time, mark DESC) AS trains_at_same_time
   FROM timetable
   )
   
SELECT MAX(trains_at_same_time) AS min_platforms
FROM cumsum;

Here’s the partial output.

Where You Can Use It:

• Daily or monthly cumulative sales
• Cumulative number of signups or activations
• Cumulative costs
• Cumulative pageviews
• Cumulative minutes watched/listened

Comparing Current vs Previous Periods With LAG()

Commonly, you’ll need to compare the values from the current and previous periods, for example, when showing monthly revenue changes. The LAG() window function will help you in this task, as it allows you to access the previous row’s value from the current row.

Example: We’ll find returning active users. They are defined as users who made a second purchase within seven days of any previous purchase. We’ll utilize LAG() in this task.

In the CTE, we list the user IDs and the order creation date. Then, we use LAG() to fetch the previous row’s value. We do that for each user separately; that’s why the data is partitioned by the user ID. Since we ordered data by creation date, the previous row equals the previous order.

In SELECT, we select the user IDs and apply filters required by the interview question.

WITH ordered_tx AS (
  SELECT user_id,
         created_at::DATE AS tx_date,
         LAG(created_at::DATE) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_tx_date
  FROM amazon_transactions
)

SELECT DISTINCT user_id
FROM ordered_tx
WHERE prev_tx_date IS NOT NULL AND tx_date - prev_tx_date <= 7;

Here’s the partial output.

Where You Can Use It:

• Daily/monthly/quarterly/annual revenue change
• Daily user retention
• Monthly user churn
• Daily/monthly/quarterly/annual sales change

Labelling Rows Using CASE WHEN

Row labeling is a common data analyst task, where you need to label rows based on business rules. The CASE WHEN statement is used for that. This is a common data analyst task where you need to label rows based on business rules.

Example: We’ll label employees with the highest and the lowest salaries.

To find the highest and the lowest salaries, we use the RANK() window function. We rank employees by salary from the lowest to the highest; those with the lowest salaries will be ranked as 1. To assign rank 1 to employees with the highest salaries, we rank them by salary in descending order.

In the SELECT statement, we select all the required columns. Then, we use CASE WHEN to label the employees. We assign the label ‘Highest Salary’ where the value in the column highest_sal (from the CTE) is 1; otherwise, the label is ‘Lowest Salary’.

In addition, we exclude all the ranks from lowest_sal and highest_sal, except rank 1. That way, we get no other employees; only those with the highest and the lowest salaries.

WITH cte AS
  (SELECT *,
          RANK() OVER(ORDER BY salary) AS lowest_sal,
          RANK() OVER(ORDER BY salary DESC) AS highest_sal
   FROM worker)
   
SELECT worker_id,
       salary,
       department,
       CASE
           WHEN highest_sal = 1 THEN 'Highest Salary'
           ELSE 'Lowest Salary'
       END AS salary_type
FROM cte
WHERE lowest_sal = 1 OR highest_sal = 1;

Here’s the output.

Where You Can Use It:

• User segmentation
• Risk scoring
• Support SLAs
• Sales pipelines
• Content platforms
• Education data

Conclusion

These SQL query examples were selected to demonstrate the business use, not so much a particular SQL concept. Of course, in some cases, the business case is tightly linked to using a particular SQL function or concept. The idea was to give you something useful; with a little bit of tweaking, you can easily adapt those queries to your particular data analysis needs.