SELECt YEAR
,MONTH
,customer_id
,max(ACCOUNT_TYPE) keep(dense_rank FIRST ORDER BY date_changed DESC) LAST_ACC
FROM (
SELECt EXTRACT(YEAR FROM DATE_CHANGED) AS YEAR,
EXTRACT(MonTH FROM DATE_CHANGED) AS MONTH,
CUSTOMER_ID,
date_changed,
account_type
FROM HISTORY
)
GROUP BY YEAR, MONTH, customer_id
ORDER BY YEAR, MONTH, CUSTOMER_ID
| YEAR | MonTH | CUSTOMER_ID | LAST_ACC |-----------------------------------------| 2013 | 1 | 200 | Premium || 2013 | 2 | 300 | Free || 2013 | 3 | 100 | Premium || 2013 | 3 | 200 | Standard |
