Well, in any case, the solution you posted there was much better than the original anyway. And here is an even better version:
Code:
SELECT
   A.USER_ID             ID        ,
   ROT.FULL_NAME         FULL_NAME ,
   A.LOCATION            LOCATION  ,
   A.PAST_WEEK           WEEK      ,
   A.PAST_MONTH          MONTH     ,
   A.PAST_QUARTER        QUARTER   ,
   A.PAST_YEAR           YEAR      ,
   A.TOTAL_DAYS          TOTAL
FROM
   (
   SELECT
      USER_ID              ,
      MAX(LOCATION)
         AS   LOCATION     ,
      COUNT(*)
         AS   TOTAL_DAYS   ,
      SUM(
         CASE WHEN LAST_UPDATE >= SYSDATE   -   7            THEN 1 END
         )
         AS   PAST_WEEK    ,
      SUM(
         CASE WHEN LAST_UPDATE >= ADD_MONTHS(SYSDATE,  -1)   THEN 1 END
         )
         AS   PAST_MONTH   ,
      SUM(
         CASE WHEN LAST_UPDATE >= ADD_MONTHS(SYSDATE,  -3)   THEN 1 END
         )
         AS   PAST_QUARTER ,
      SUM(
         CASE WHEN LAST_UPDATE >= ADD_MONTHS(SYSDATE, -12)   THEN 1 END
         )
         AS   PAST_YEAR
   FROM
      ACCOUNTS
   WHERE
      LAST_UPDATE >= ADD_MONTHS(SYSDATE, -12)
   GROUP   BY
      USER_ID
   )                  A   ,
   ROW_OWNER_TABLE   ROT
WHERE
   ROT.USER_ID   =   A.USER_ID
ORDER   BY
   PAST_WEEK   ,
   PAST_MONTH
This version will only hit the Accounts table once, much like your second statement, but I made further changes:

- DECODE -> CASE
...- CASE is more readable
...- CASE is faster
- Moved GROUP BY
...- I waited until after the GROUP BY to join to the ROT table, saving time
- Added WHERE clause
...- Since you are only interested in records from the past year, this predicate will make it faster.
- Used ADD_MONTHS, which is more precise, based on your final names. You called the field MONTH, but it was actually the past 30 days, which is not always a month.

You should still look into that MAX(LOCATION) thing

- Chris