How can we set a custom sound for push notifications?

To add a custom sound to Android 8.0 and above, you should use Custom Channels. You can create a custom channel under Settings > Mobile Settings > Android Channels > Create Channel. You can select the Custom Channels you create at the bottom of the Edit Campaign Options step.

Refer to Campaign Options - Notification Sounds.