SQL to calculate number of values for each day based on a max value from previous date

Question

Aryan Kumar · Answer

Sure, here is the SQL to calculate the number of values for each day based on a max value from the previous date:

SQL

SELECT
  date,
  COUNT(*) AS num_values
FROM
  (
    SELECT
      date,
      MAX(value) AS mavalue
    FROM
      my_table
    GROUP BY
      date
  ) AS t
GROUP BY
  date
HAVING
  mavalue IS NOT NULL
ORDER BY
  date;

This query will first calculate the maximum value for each day in the my_table table. It will then group the rows by date and count the number of rows for each date. The HAVING clause ensures that only days with a maximum value are included in the results. The ORDER BY clause sorts the results by date.

The following is an example of the output of the query:

date | num_values
------- | --------
2023-01-01 | 10
2023-01-02 | 20
2023-01-03 | 30

forum

SQL to calculate number of values for each day based on a max value from previous date

Can you answer this question?

1 Answers