[El Sanchez]

у меня информация в таблице с множеством разных пк и получить информацию нужно о всех, что содержаться в таблице, а не только для "name1". »

Griboed0ff,

Код:

SELECT *
FROM (
    SELECT WorkstationName,
        sum(user_logon_count) OVER win AS all_logon,
        nth_value(TargetUserName, 1) OVER win AS "user 1",
        nth_value(user_logon_count, 1) OVER win AS "user 1 logon",
        nth_value(TargetUserName, 2) OVER win AS "user 2",
        nth_value(user_logon_count, 2) OVER win AS "user 2 logon",
        nth_value(TargetUserName, 3) OVER win AS "user 3",
        nth_value(user_logon_count, 3) OVER win AS "user 3 logon"
    FROM (
        SELECT *, row_number() OVER (
            PARTITION BY WorkstationName
            ORDER BY user_logon_count DESC, time_created DESC) AS rn
        FROM (
            SELECT *,
                printf('%s-%s-%s %s:00',
                    substr(TimeCreated, 7, 4),
                    substr(TimeCreated, 4, 2),
                    substr(TimeCreated, 1, 2),
                    substr(TimeCreated, 12)
                ) AS time_created,
                count(1) OVER (PARTITION BY WorkstationName, TargetUserName) AS user_logon_count
            FROM pc_users
            WHERE time_created > datetime('now', '-100 days')
            ORDER BY WorkstationName, user_logon_count DESC, time_created DESC
        ) GROUP BY WorkstationName, TargetUserName
    ) WHERE rn <= 3
    WINDOW win AS (PARTITION BY WorkstationName)
) GROUP BY WorkstationName