Stock Rise to the Limit for 3 Consecutive Days in a Month

Problem

To list the daily closing price record of a stock exchange in a month, in which the , CODE column is used to contain the stock code, DT is the date, and CL is the closing price level.

Please select out the stock rise to the limit for 3 consecutive days in this month. In order to avoid the error caused by rounding off, the rate of rise-up limit is 9.5%.

Tip

Rough train of thought: Sort the record by code and date, and group by the stock code. Thus, you will get a monthly price list of a stock. In this way, you can easily compute the rate of ups and downs for each stock. By comparing the rate of ups and downs, you can judge if the stock rises to its limit and make the final statistics on stocks rising to its limit for 3 consecutive days.

1. First, add a field to each record in the journal table to record the rate of ups and downs comparing with that of the previous day. The value of this field is left empty temporarily.
2. Sort by code and date in the table. The purpose of this operation is to guarantee the grouped sequence in the step followed. In each group, the data is sorted by date.
3. Group by stock code in the table. Because the table is already sorted by code, no sorting will be performed again.
4. For each record in each group, work out the rate of ups and downs. Please note if this is the first record in the group, then there will be no previous record. The rise and drop shall be taken as 0.

Select the stock rising to its limit for consecutive 3 days for once or above times, and retrieve the code.

Code

Result