How do I get the delta for user_id since the previous months using oracle sql

Let's say I have a table that has "user_id, loaddate", and many users are in user_id, but some may be added or removed at each month. How can i compare to find out who has been added or dropped out to the previous month´s? I suppose it would be gettin开发者_高级运维g a delta over the previous month for each and every month.

Presuming LOADDATE represents the date when a user was added to your system, then the delta of the newbies is easy to calculate. The wrinkle is finding the users who have been removed. There is no way to do this by default.

The common way to do this is to maintain an audit or history of the activity on tables of interest, usually by having a parallel history table which is populated by DML triggers.

The more expensive way of doing this is to splash out for Oracle's moderatley expensive - but highly neat - Total Recall product (AKA Flashback database). Find out more.

To find out who has been added, you could look for user_ids whose loaddate is within the last month. (Or user_ids whose loaddates are all within the last month, if a user can have multiple loaddates). Oracle databases have no memory of their previous state, so there is no way to look for things that used to be in the database but aren't anymore. Maybe you could get users who dropped out by looking for users who have no loaddate within the last month, depending on exactly how your table works. (Do you delete users when they drop out?)