Understanding the Problem

We are given a query that aims to retrieve rows from the daily_price table where two conditions are met:

• The close price of the current day is greater than the open price of the same day.
• The close price of the current day is also greater than the high price of the previous day.

The goal is to find all rows that satisfy both conditions on a specific date, in this case, August 31st, 2022.

Background Information

Before we dive into the solution, let’s understand some key concepts and data structures involved:

• SQL Subqueries: A subquery is a query nested inside another query. In our problem, we have two subqueries: one to get today’s close price (SELECT close FROM daily_price WHERE date = '2022-08-31') and another to get yesterday’s high price (SELECT high FROM daily_price WHERE date = '2022-08-30').
• SQL Window Functions: SQL window functions, such as LAG, allow us to access data from other rows in the same result set. In our solution, we use LAG to compare today’s close with yesterday’s high.

Solution Overview

To solve this problem, we will:

1. Use a subquery to get the current day’s close and previous day’s high prices.
2. Apply these values as conditions in our main query using AND operators.
3. Use SQL window functions (LAG) to compare today’s close with yesterday’s high.

Step-by-Step Solution

Let’s break down the solution step by step:

Using Subqueries

The initial approach attempts to use subqueries to achieve the desired result:

SELECT * 
FROM daily_price 
WHERE close > open
AND date = '2022-08-31' 
AND (SELECT close FROM daily_price WHERE date = '2022-08-31') > (SELECT high FROM daily_price WHERE date = '2022-08-30')

However, this approach is not efficient and can lead to performance issues due to the repeated use of subqueries.

Using SQL Window Functions

A better approach uses SQL window functions (LAG) to compare today’s close with yesterday’s high:

SELECT symbol  
       ,date    
       ,open    
       ,high    
       ,low 
       ,close
FROM    (
        SELECT *
              ,LAG(high)    OVER (PARTITION BY symbol ORDER BY date)      AS pre
        FROM   t
    ) t

WHERE   close > pre 
  AND   close > open 
  AND   date = '2022-08-31'

In this solution, we first use a subquery to get the previous day’s high (LAG function). We then apply these values as conditions in our main query.

How It Works

Let’s analyze how this solution works:

• The outer query selects all columns from the daily_price table.
• The subquery (using window function LAG) calculates the previous day’s high for each symbol and orders it by date.
• In the main query, we filter rows where today’s close is greater than yesterday’s high (close > pre) and also greater than the open price (close > open).
• We only consider dates equal to ‘2022-08-31’.

Example Walkthrough

To better understand this solution, let’s walk through an example:

Suppose we have a daily_price table with data like this:

| symbol | date       | open | high | low | close |
| ---   | ---        | ---  | ---  | --- | ---  |
| ACR   | 2022-08-30 | 0.061| 0.063| 0.06| 0.06  |
| ACR   | 2022-08-31 | 0.066| 0.07 | 0.066| 0.07  |
| NBI   | 2022-08-30 | 1.52 | 1.52 | 1.51| 1.52  |
| NBI   | 2022-08-31 | 1.51 | 1.52 | 1.505| 1.515|

Now, let’s apply the query:

SELECT symbol  
       ,date    
       ,open    
       ,high    
       ,low 
       ,close
FROM    (
        SELECT *
              ,LAG(high)    OVER (PARTITION BY symbol ORDER BY date)      AS pre
        FROM   daily_price
    ) t

WHERE   close > pre 
  AND   close > open 
  AND   date = '2022-08-31'

This yields the following result:

| symbol | date       | open | high | low | close |
| ---   | ---        | ---  | ---  | --- | ---  |
| ACR   | 2022-08-31 | 0.066| 0.07 | 0.066| 0.07  |

As expected, only the row for ACR on August 31st satisfies both conditions.

Conclusion

In conclusion, using SQL window functions (LAG) can efficiently solve complex queries involving multiple dates and aggregations. By applying this approach to our problem, we were able to find all rows that meet two specific conditions: today’s close price is greater than the open price of the same day and also greater than yesterday’s high price.

Last modified on 2023-12-20