I have a table of results similar to below:
How do I query for tickers which are increasing in both latest_price and sentiment_change over time. For example you can see AMSC has increased in both latest_price and sentiment_change. I am using postgresql thanks for your help
"created_at" "ticker" "title" "latest_price" "sentiment_change"
"2020-05-24 06:41:40.948862" "AMSC" "American Superconductor Corporation" "6.66" "5.56"
"2020-05-24 06:42:02.987018" "AMSC" "American Superconductor Corporation" "7.66" "6.56"
"2020-05-24 07:59:08.014871" "AMSC" "American Superconductor Corporation" "8.66" "7.27"
"2020-05-24 08:00:23.406509" "AMSC" "American Superconductor Corporation" "9.66" "8.27"
"2020-05-24 08:04:02.881144" "AMSC" "American Superconductor Corporation" "10.66" "9.27"
"2020-05-24 06:41:51.797114" "AUPH" "Aurinia Pharmaceuticals Inc." "16.24" "-0.37"
"2020-05-24 06:41:52.768141" "CGC" "Canopy Growth Corporation " "19.42" "-0.89"
"2020-05-24 06:42:06.947722" "COF" "Capital One Financial Corp." "60.21" "9.19"
"2020-05-24 07:58:49.696729" "CNC" "Centene Corp." "64.84" "0.0"
"2020-05-24 08:00:05.909404" "CNC" "Centene Corp." "64.84" "0.0"
I created a db-fiddle for this here: https://www.db-fiddle.com/f/37qeZWoFpVLkJLqN16TirX/0
If your version of PostgreSQL supports LAG you can use the query below to get started. I've done the increasing parts, you can do the decreasing ones.
You can then, for example, count the number of times the latest_price or sentiment_change increases/decreases in relation to the number of observations you have; and if above/below a certain threshold determine your course of action.
SELECT
created_at,
ticker,
latest_price,
sentiment_change,
LAG(latest_price,1) OVER (
PARTITION BY ticker
ORDER BY created_at) AS previous_price,
CASE WHEN latest_price >= LAG(latest_price, 1) OVER (
PARTITION BY ticker
ORDER BY created_at)
THEN 1 ELSE 0 END AS increased_price_flag,
LAG(sentiment_change,1) OVER (
PARTITION BY ticker
ORDER BY created_at) AS previous_sentiment_change,
CASE WHEN sentiment_change >= LAG(sentiment_change, 1) OVER (
PARTITION BY ticker
ORDER BY created_at)
THEN 1 ELSE 0 END AS increased_sentiment_change_flag
FROM results
ORDER BY ticker, created_at;
Result:
| created_at | ticker | latest_price | sentiment_change | previous_price | increased_price_flag | previous_sentiment_change | increased_sentiment_change_flag |
| ------------------------ | ------ | ------------ | ---------------- | -------------- | -------------------- | ------------------------- | ------------------------------- |
| 2020-05-24T06:41:40.948Z | AMSC | 6.66 | 5.56 | | 0 | | 0 |
| 2020-05-24T06:42:02.987Z | AMSC | 7.66 | 6.56 | 6.66 | 1 | 5.56 | 1 |
| 2020-05-24T07:59:08.014Z | AMSC | 8.66 | 7.27 | 7.66 | 1 | 6.56 | 1 |
| 2020-05-24T08:00:23.406Z | AMSC | 9.66 | 8.27 | 8.66 | 1 | 7.27 | 1 |
| 2020-05-24T08:04:02.881Z | AMSC | 10.66 | 9.27 | 9.66 | 1 | 8.27 | 1 |
| 2020-05-24T06:41:51.797Z | AUPH | 16.24 | -0.37 | | 0 | | 0 |
| 2020-05-24T06:41:52.768Z | CGC | 19.42 | -0.89 | | 0 | | 0 |
| 2020-05-24T07:58:49.696Z | CNC | 64.84 | 0.00 | | 0 | | 0 |
| 2020-05-24T08:00:05.909Z | CNC | 64.84 | 0.00 | 64.84 | 1 | 0.00 | 1 |
| 2020-05-24T06:42:06.947Z | COF | 60.21 | 9.19 | | 0 | | 0 |