You can also use Common Table Expressions to create temporary tables which you can query from / join against. This is typically my preferred approach when I want to take one bit of data, then manipulate it in incremental steps:

WITH
macos_admin_users AS (
  SELECT 
    username, 
    uid 
  FROM users, user_groups USING(uid) 
  WHERE user_groups.gid = 80)
SELECT 
  username, 
  uid,
  datetime(password_last_set_time, 'unixepoch') AS password_last_set
FROM account_policy_data, macos_admin_users USING (uid);

+-------------+-----+---------------------+
| username    | uid | password_last_set   |
+-------------+-----+---------------------+
| fritz       | 502 | 2021-07-10 14:28:15 |
| kolide-imac | 501 | 2021-06-18 13:18:33 |
| root        | 0   |                     |
+-------------+-----+---------------------+

In the above example I am looking for any user accounts which belong to group 80 (the macOS admin group) and then I am joining against that output to query the

account_policy_data

table which contains details like password set time.

Most of the time you can accomplish similar outcomes with less verbose approaches that rely simply on clever joins, but CTEs can be a great way to compartmentalize your query.