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

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

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.