SQLAlchemy: Get the maximum value of grouped values

Question

I have this table in my Flask app:

class Events(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    timestamp_event = db.Column(db.DateTime, nullable=False)

I need to get the day with the most records, including the value and the date itself in a printable format (e.g. d/m/y). I tried the following code:

daily_max = db.func.max(db.session.query(db.func.count(Events.id),\
                        db.func.date_format(Events.timestamp_event,'%d/%m/%y'))\
                        .group_by(db.func.date_format(Events.timestamp_event,'%Y%M%D')))

Then we hope that daily_max[0] has a value and daily_max[1] does not have the date of hh:mm:ss.

When I print daily_max after the query, the value is:

daily_max = max((SELECT count(events.id) AS count_1, date_format(events.timestamp_event, :date_format_2)
                  AS date_format_1
                  FROM events GROUP BY date_format(events.timestamp_event, :date_format_3)))

This is a sample of the data. The answer (the value of daily_max) should be (3, "21/01/2022"):

id timestamp_event
---- ---------------
1 2022-01-15 12:34
2 2022-01-21 01:23
3 2022-01-21 05:33
4 2022-01-21 11:11
5 2022-01-23 00:01
6 2022-01-23 23:29

Any clues as to what I'm doing wrong? Can't find the answer in the documentation.

Answer 1

This is a bad (inefficient) approach:

daily_max = db.session.query(db.func.count(Events.id),
            db.func.date_format(Events.timestamp_event,'%d/%m/%y'))\
            .group_by(db.func.date_format(Events.timestamp_event,'%Y%M%D'))\
            .order_by(db.func.count(Events.id).desc()).first()

It groups all (id count, date) pairs, sorts the pairs in reverse order of count, and gets the first pair.

It gets the job done, but I'd really like to know an efficient, elegant way to use func.max().