Given the following data set example, how should I structure my SQL query to determine if the value has gradually grown day-by-day given a time range in the query for a specific user_id by returning a true/false result
| user_id | date | value |
|---|---|---|
| 1 | 4/1/2021 12:00 | 350 |
| 1 | 4/2/2021 12:00 | 400 |
| 1 | 4/3/2021 12:00 | 450 |
| 5 | 4/4/2021 12:00 | 560 |
| 5 | 4/5/2021 12:00 | 566 |
| 5 | 4/6/2021 12:00 | 483 |
| 5 | 4/7/2021 12:00 | 570 |
Given the above example, we can observe that the value for user_id=1 has gradually grown day after day while the value for user_id=5 has grown for 2 consecutive days, but bounced down on the third day.
Therefore, the expected result-set for user_id=1 and user_id=5 would be:
| user_id | value_is_increasing |
|---|---|
| 1 | true |
The expected result-set for user_id=5 would be:
| user_id | value_is_increasing |
|---|---|
| 5 | false |
Declaration Update: I would like to determine if the value has constantly grown within a specific time range in a day-by-day manner (consecutive). Moreover, individual value points should be considered noisy (they bounce up and down even though they trend upward or downward over time) and affect the result.
Advertisement
Answer
Use aggregation:
SELECT user_id,
GROUP_CONCAT(value ORDER BY date) = GROUP_CONCAT(value ORDER BY value) value_is_increasing
FROM tablename
GROUP BY user_id
HAVING COUNT(*) > 1
AND COUNT(*) = COUNT(DISTINCT value)
The condition COUNT(*) > 1 will return only users that have more than 1 values in the table.
The condition COUNT(*) = COUNT(DISTINCT value) will return only users that have only distinct values in the table.
GROUP_CONCAT(value ORDER BY date) will return values order by date and
GROUP_CONCAT(value ORDER BY value) will return values order by value.
If the results of the 2 GROUP_CONCAT() are the same this means that values are monotonically increasing.
See the demo.