I have a table with offers where I perform typical CRUD actions.
Each offer can has one out of the following statuses: active, deleted, archived, withdrawn, etc.
Most actions I perform only for active offers, so is it a good idea to move offers with other statuses to other tables, like offers_archived, offers_withdrawn?
Data has a place to be based on what it is. Moving it somewhere else such as to an archive table or a deleted table is an implementation detail -- a workaround of sorts for performance.
Some databases, like PostgreSQL 10, can gracefully do this under the hood with List Partioning. In doing so, the "deleted" values are stored in a different partition (another distinct table is made available to the user), but it doesn't get in the way.
CREATE TABLE offers (...) PARTITION BY LIST (status);
CREATE TABLE offers_deleted PARTITION OF offers
FOR VALUES IN ('deleted');
Now you could query offers WHERE status='deleted', and under the hood it would focus on just that one partition. Remove that predicate, and you'd query all of the partitions of offers. Or, you could query offers_deleted directly.
Even there though, my advice would generally be it's bad practice to move data because the status is deleted or because it's archived, unless performance demands it. Then you don't do it because you want to, you do it because you have to.