-- Last month

SELECT
    AVG(max_daily_peek_power) AS average_monthly_peek_power
FROM (
    SELECT
        DATE_FORMAT(FROM_UNIXTIME(last_updated_ts), "%Y%-%m-%d") AS tariff_date,
        MAX(CONVERT(state, UNSIGNED)) AS max_daily_peek_power
    FROM
        states
        LEFT JOIN state_attributes ON (
        states.attributes_id = state_attributes.attributes_id
        )
    WHERE
        metadata_id = (
                SELECT metadata_id
                FROM states_meta
                WHERE entity_id = "sensor.mains_power_hourly_tariff"
        )
        AND state > 0
        AND last_updated_ts >= UNIX_TIMESTAMP(DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 2 MONTH)), INTERVAL 1 DAY))
        AND last_updated_ts < UNIX_TIMESTAMP(DATE_ADD(LAST_DAY(DATE_SUB(NOW(),  INTERVAL 1 MONTH)), INTERVAL 1 DAY))
    GROUP BY tariff_date
    ORDER BY max_daily_peek_power DESC
    LIMIT 3
) AS max_power_per_date;



-- Current month

SELECT
    AVG(max_daily_peek_power) AS average_monthly_peek_power
FROM (
    SELECT
        DATE_FORMAT(FROM_UNIXTIME(last_updated_ts), "%Y%-%m-%d") AS tariff_date,
        MAX(CONVERT(state, UNSIGNED)) AS max_daily_peek_power
    FROM
        states
        LEFT JOIN state_attributes ON (
        states.attributes_id = state_attributes.attributes_id
        )
    WHERE
        metadata_id = (
                SELECT metadata_id
                FROM states_meta
                WHERE entity_id = "sensor.mains_power_hourly_tariff"
        )
        AND state > 0
        AND last_updated_ts >= UNIX_TIMESTAMP(DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH)), INTERVAL 1 DAY))
        AND last_updated_ts < UNIX_TIMESTAMP(DATE_ADD(LAST_DAY(DATE_SUB(NOW(),  INTERVAL 0 MONTH)), INTERVAL 1 DAY))
    GROUP BY tariff_date
    ORDER BY max_daily_peek_power DESC
    LIMIT 3
) AS max_power_per_date;