trusted_device_details
|DEVICE_TYPE|DEVICE_NAME| DEVICE_ID | Created Date Time | Last Accessed Date Time | IP_ADDRESS | USER_ID | CLIENT_CODE | RANK_NUMBER
|———–|———–| ———- | —————– | ———————– | ———– | ——–| ————| ————
|MOBIL APP |IPHONE 6S | 4455555 | 8-Dec-24 | 12/8/2024 16:01 | 10.1.1.1 | XXX1 | 55111444 | 1
|MOBIL APP |IPHONE 15 | 52344444 | 8-Dec-24 | 12/8/2024 16:01 | 10.1.1.6 | XXX1 | 55111444 | 1
|MOBIL APP |Ipad | 788995544 | 9-Nov-24 | 12/6/2025 16:01 | 10.1.1.1 | XXX1 | 55111444 | 1

USER_AUDIT_TRAIL
|DEVICE_TYPE|DEVICE_NAME| DEVICE_ID | Created Date Time | Last Accessed Date Time | IP_ADDRESS | USER_ID | CLIENT_CODE | RANK_NUMBER
|———–|———–| ———- | —————– | ———————– | ———– | ——–| ————| ————
|MOBIL APP |IPHONE 6S | 4455555 | 10/8/2024 | | | XXX1 | 55111444 | 1
|MOBIL APP |IPHONE 15 | 52344444 | 11/8/2024 | | | XXX1 | 55111444 | 1
|MOBIL APP |Ipad | 788995544 | 12/9/2024 | | | XXX1 | 55111444 | 1

Result:
|DEVICE_TYPE|DEVICE_NAME| DEVICE_ID | Created Date Time | Last Accessed Date Time | IP_ADDRESS | USER_ID | CLIENT_CODE | RANK_NUMBER
|———–|———–| ———- | —————– | ———————– | ———– | ——–| ————| ————
|MOBIL APP |IPHONE 6S | 4455555 | 8-Dec-24 | 12/8/2024 16:01 | 10.1.1.1 | XXX1 | 55111444 | 1
|MOBIL APP |IPHONE 15 | 52344444 | 8-Dec-24 | 12/8/2024 16:01 | 10.1.1.6 | XXX1 | 55111444 | 1
|MOBIL APP |Ipad | 788995544 | 9-Nov-24 | 12/6/2025 16:01 | 10.1.1.1 | XXX1 | 55111444 | 1
|MOBIL APP |IPHONE 6S | 4455555 | 10/8/2024 | | | XXX1 | 55111444 | 1
|MOBIL APP |IPHONE 15 | 52344444 | 11/8/2024 | | | XXX1 | 55111444 | 1
|MOBIL APP |Ipad | 788995544 | 12/9/2024 | | | XXX1 | 55111444 | 1

I have tried to combine these two queries and get a result using union it’s not working for me. Could you please help get the above-mentioned result.

–Query#1
WITH devicerank AS (
SELECT
t.channel,
t.user_id,
t.device_name,
t.device_id,
t.created_at,
t.last_accessed_at,
ROW_NUMBER()
OVER(PARTITION BY user_id, device_id
ORDER BY
t.last_accessed_at DESC
) AS rank_number
FROM
trusted_device_details t
)
SELECT
channel AS device_type,
device_name AS device_name,
device_id AS device_id,
created_at AS “Created Date Time”,
last_accessed_at AS “Last Accessed Date Time ”,
NULL AS ip_address,
user_id,
NULL AS “CLIENT_CODE”,
rank_number
FROM
devicerank
WHERE
rank_number = 1
;
–Query#2
WITH devicerank AS (
SELECT
application_id,
user_id,
client_code,
device_name,
device_id,
ip,
TIMESTAMP,
ROW_NUMBER()
OVER(PARTITION BY user_id, device_id
ORDER BY
u.timestamp DESC
) AS rank_number
FROM
user_audit_trail u
)
SELECT
application_id AS device_type,
device_name AS device_name,
device_id AS device_id,
TIMESTAMP AS “Created Date Time”,
NULL AS “Last Accessed Date Time ”,
ip AS ip_address,
user_id,
client_code AS “CLIENT_CODE”,
rank_number
FROM
devicerank
WHERE
rank_number = 1