How can I do such as that effect for my water sprite in Unity?

Questions & Answers
1

Sorry for link, Unity Answers don’t want to upload .gif with error of parsing :]

2

UnityVersions
Go to the version you’re working on, and on the dropdown select the Built In Shaders option.
You can then replicate the Offset parameter from Default diffusse shader and put it into a copy of the default sprite shader.
The trick is to change that offset everyframe.