Repeated rows because ZDEVICEID is not unique in the ZSYNCPEER table.

[mjpieters]

The LEFT OUTER JOIN ZSYNCPEER join leads to repeated time entries, because multiple rows in ZSYNCPEER have the same ZDEVICEID. Moreover, many of these rows have a NULL ZMODEL column.

In my screentime database, that leads to more than 8x as many rows as there are actual events.

You could work around this with a CTE pulling out the unique model names first:

        WITH model_names AS (
            SELECT ZDEVICEID, ZMODEL
            FROM ZSYNCPEER
            WHERE ZMODEL IS NOT NULL
            GROUP BY ZDEVICEID
        )
        SELECT
            ZOBJECT.ZVALUESTRING AS "app",
            (ZOBJECT.ZENDDATE - ZOBJECT.ZSTARTDATE) AS "usage",
            (ZOBJECT.ZSTARTDATE + 978307200) as "start_time",
            (ZOBJECT.ZENDDATE + 978307200) as "end_time",
            (ZOBJECT.ZCREATIONDATE + 978307200) as "created_at",
            ZOBJECT.ZSECONDSFROMGMT AS "tz",
            ZSOURCE.ZDEVICEID AS "device_id",
            model_names.ZMODEL AS "device_model"
        FROM
            ZOBJECT
            LEFT JOIN ZSOURCE
                ON ZOBJECT.ZSOURCE = ZSOURCE.Z_PK
            LEFT JOIN model_names
                ON ZSOURCE.ZDEVICEID = model_names.ZDEVICEID
        WHERE
            ZSTREAMNAME = "/app/usage" AND
            (ZOBJECT.ZCREATIONDATE + 978307200) > ?
        ORDER BY
            ZCREATIONDATE DESC