SQL execution order and exercises

✅ SQL Logical Execution Order (most important version)

1. FROM

2. ON

3. JOIN

4. WHERE

5. GROUP BY

6. HAVING

7. SELECT

8. DISTINCT

9. WINDOW functions (e.g., LAG/LEAD)

10. ORDER BY

11. LIMIT / OFFSET

Ex 1

visit_id is the column with unique values for this table. This table contains information about the customers who visited the mall.

transaction_id is column with unique values for this table. This table contains information about the transactions made during the visit_id.

Write a solution to find the IDs of the users who visited without making any transactions and the number of times they made these types of visits.

Return the result table sorted in any order.

The result format is in the following example.

# Write your MySQL query statement below
SELECT
  customer_id,
  COUNT(visit_id) AS count_no_trans
FROM
  Visits
WHERE
  visit_id NOT IN (
    SELECT
      visit_id
    FROM
      Transactions
  )
GROUP BY
  customer_id

Ex 2

id is the column with unique values for this table. There are no different rows with the same recordDate. This table contains information about the temperature on a certain day.

Write a solution to find all dates’ id with higher temperatures compared to its previous dates (yesterday).

# Write your MySQL query statement below
SELECT id
FROM
    (SELECT
        id,
        temperature,
        recordDate,
        lag(temperature) OVER(ORDER BY recordDate) AS prev_temp
FROM Weather) t
WHERE temperature > prev_temp

Back to top