SQLite Forum

How to convert to CTE
Login
Perhaps you need to state what you want in English first.

For example, your original query translates into the following:

Consider a table reg_history_all which contains the columns id, date_status_added, and reg_status.  For each id in the table find the max date_status_added excluding all id that ever have reg_status 'Deceased' but only consider records where the reg_status_added is less than 43000.  Include only results where at least one record in the group has reg_status 'Registered'.  Then count the number of resulting rows.