CODEWITHSUNDEEP
mysql
Carol.Kar
Carol.Kar

Reputation: 5345

Get return for the latest day

I am running a mysql - 10.1.39-MariaDB - mariadb.org binary- database.

I am having the following table:

| id | date                | product_name | close |
|----|---------------------|--------------|-------|
| 1  | 2019-08-07 00:00:00 | Product 1    | 806   |
| 2  | 2019-08-06 00:00:00 | Product 1    | 982   |
| 3  | 2019-08-05 00:00:00 | Product 1    | 64    |
| 4  | 2019-08-07 00:00:00 | Product 2    | 874   |
| 5  | 2019-08-06 00:00:00 | Product 2    | 739   |
| 6  | 2019-08-05 00:00:00 | Product 2    | 555   |
| 7  | 2019-08-07 00:00:00 | Product 3    | 762   |
| 8  | 2019-08-06 00:00:00 | Product 3    | 955   |
| 9  | 2019-08-05 00:00:00 | Product 3    | 573   |

I want to get the following output:

| id | date                | product_name | close | daily_return |
|----|---------------------|--------------|-------|--------------|
| 4  | 2019-08-07 00:00:00 | Product 2    | 874   | 0,182679296  |
| 1  | 2019-08-07 00:00:00 | Product 1    | 806   | -0,179226069 |

Basically I want ot get the TOP 2 products with the highest return. Whereas return is calculated by (close_currentDay - close_previousDay)/close_previousDay for each product.

I tried the following:

SELECT
    *,
    (
        CLOSE -(
        SELECT
            (t2.close)
        FROM
            prices t2
        WHERE
            t2.date < t1.date
        ORDER BY
            t2.date
        DESC
    LIMIT 1
    )
    ) /(
    SELECT
        (t2.close)
    FROM
        prices t2
    WHERE
        t2.date < t1.date
    ORDER BY
        t2.date
    DESC
LIMIT 1
) AS daily_return
FROM
    prices t1
WHERE DATE >= DATE(NOW()) - INTERVAL 1 DAY

Which gives me the return for each product_name.

How to get the last product_name and sort this by the highest daily_return?

Upvotes: 0

Views: 79

Answers (2)

Dark Knight
Dark Knight

Reputation: 6531

Problem Statement: Find the top 2 products with the highest returns on the latest date i.e. max date in the table.

Solution:

  • If you have an index on date field, it would be super fast.
  • Scans table only once and also uses date filter(index would allow MySQL to only process rows of given date range only.
  • A user-defined variable @old_close is used to find the return. Note here we need sorted data based on product and date.
SELECT * 
FROM (
    SELECT
        prices.*,
      CAST((`close` - @old_close) / @old_close AS DECIMAL(20, 10))  AS daily_return, -- Use @old_case, currently it has value of old row, next column will set it to current close value.
      @old_close:= `close`    -- Set @old_close to close value of this row, so it can be used in next row
   FROM prices
   INNER JOIN (
        SELECT 
            DATE(MAX(`date`)) - INTERVAL 1 DAY AS date_from,  -- if you're not sure whether you have date before latest date or not, can keep date before 1/2/3 day.
            @old_close:= 0 as o_c
        FROM prices
    ) AS t ON prices.date >= t.date_from
   ORDER BY product_name, `date` ASC    
) AS tt
ORDER BY `date` DESC, daily_return DESC
LIMIT 2;

Another version which doesn't depend on this date parameter.

SELECT * 
FROM (
    SELECT
        prices.*,
        CAST((`close` - @old_close) / @old_close AS DECIMAL(20, 10))  AS daily_return, -- Use @old_case, currently it has value of old row, next column will set it to current close value.
        @old_close:= `close`    -- Set @old_close to close value of this row, so it can be used in next row
    FROM prices,
    (SELECT @old_close:= 0 as o_c) AS t 
    ORDER BY product_name, `date` ASC 
) AS tt
ORDER BY `date` DESC, daily_return DESC
LIMIT 2

Upvotes: 1

forpas
forpas

Reputation: 164064

You can do it with a self join:

select 
  p.*, 
  cast((p.close - pp.close) / pp.close as decimal(20, 10)) as daily_return 
from prices p left join prices pp
on p.product_name = pp.product_name 
   and pp.date = date_add(p.date, interval -1 day)
order by p.date desc, daily_return desc, p.product_name 
limit 2

See the demo.
Results:

| id  | date                | product_name | close | daily_return |
| --- | ------------------- | ------------ | ----- | ------------ |
| 4   | 2019-08-07 00:00:00 | Product 2    | 874   | 0.182679296  |
| 1   | 2019-08-07 00:00:00 | Product 1    | 806   | -0.179226069 |

Upvotes: 1

Related Questions