Depends on where you live. If you live in a rural area, the tap water that goes down the drain probably flows to an on-site sewage treatment system for your house, such as a septic tank and absorption field or an aerobic treatment system. The on-site system partially treats the water, and the soil structure and bacteria finish cleaning it, with the water eventually evaporating, being taken up by plants, or percolating down to the groundwater table.
In a city, the tap water flowing into the drain flows to a sewage treatment plant where it is partially cleaned and usually disinfected (with chlorine). After the chlorine is removed, it is discharged to a river or stream, eventually flowing downstream to another town's intake, or into larger rivers and into the ocean. In this scenario, the sewage treatment plant partially cleans the water, and natural processes and organisms in the water body (sunlight, oxygen, microorganisms, etc.).
Lately, however, there have been some cities that have been reusing the treated wastewater for irrigation for such things as golf courses or pasture land. And in California, there is at least one instance of treated water being pumped back into the aquifer to recharge the groundwater. But these occurrences are still pretty rare.