We want to delete all subscriber records from "All Subscribers" which are not "Unsubscribed" or "Bounced" and created before last six months. How can we do it using automation studio?
First, you need to create a sendable data extension, which at least should contain your subscriber key. This will be populated by querying Subscribers Data View, with your criteria defined in the SQL Query. Based on your use case, you should filter on fields: DateJoined and Status.
Once you have found the subscribers you want to delete, you can use the Contact Deletion Framework, which will remove the subscribers across your SFMC. This approach can be automated with SSJS/Script Activity in Automation Studio, as I provide an example of in this thread: Marketing Cloud SSJS: trying to automate contact deletion - which utilises DeleteByListReference endpoint. This SSJS should reference the above data extension, deleting contacts listed within.