First, it helps to know that GPUs always evaluate fragment/pixel shaders on 2x2 blocks of pixels at a time. (Even if only some of those pixels ultimately need to be drawn, while others are outside the polygon or occluded - the unneeded fragments are masked out instead of being written at the end).
The screenspace derivative of a variable (or expression) v in your shader is the difference in the value of v (at that point in the code) from one side of this 2x2 pixel quad to the other. ie. ddx is the value of v in the right pixel minus the value of v in the left, and similarly for ddy on the vertical. (Here I'm using the names for these functions in DirectX HLSL. In OpenGL GLSL, these are dFdx and dFdy respectively.)
This answers "how fast does v increase or decrease as we move horizontally (ddx) or vertically (ddy) across the screen?" - ie. in calculus terms, it approximates the partial derivatives of your variable (approximate because it uses discrete samples at each fragment, rather than mathematically evaluating the infinitesimal behaviour of the function)
For scalar quantities, we can also view this as a gradient vector ∇v = float2(ddx(v), ddy(v)) which points along the screenspace direction in which v is increasing most rapidly.
This type of information is often used internally to select an appropriate mipmap or anisotropic filtering kernel for texture lookups. For example, if my camera looks almost parallel to the vertical uv direction of a textured floor plane, ddy(uv.y) will be very large compared to ddx(uv.x) (since the vertical axis is foreshortened on the screen - one pixel stride vertically covers a longer stretch of texture space), which tells the texture sampling hardware that I need anisotropic filtering to blur the vertical texture direction more than the horizontal to avoid aliasing artifacts.
For most simple effects you don't need to use these derivatives, since the basic 2D texture sampling methods handle it for you. But as Maximus Minimus mentions in a comment above, when you're distorting your texture lookups, you might need to manually retrieve and/or massage the screenspace derivatives to use, to help the hardware select the appropriate filtering (eg. via tex2Dlod in HLSL).
This can come up when you're using polar or spherical coordinates to wrap a texture circularly. At the wrap-around point, adjacent pixels suddenly jump from -180° to +180° angles, which sit at opposite ends of the texture, so the screenspace derivative for the texture coordinate gets huge, and the default pipeline will try to select a tiny mip to hide the aliasing - introducing a blurry 2-pixel-wide seam. So we have to be aware of these derivatives to tell it otherwise (example for polar coordinates on a quad / example on a sphere in shader graph and in code).
I also had to muck with derivatives to avoid seams between tiles in this single-quad tilemap shader.
Screenspace decals are another such case, where a single 2x2 block can cover a large jump discontinuity in the calculated texture coordinate, leading to a smeared or aliased edge if you let the system calculate the filtering level naively. This article goes into detail about this artifact and approaches to mitigate it. This article shows a similar issue and fix when rendering a procedural scene with raymarching.
These derivatives can also be handy when you're using noise functions in procedural texture generation. If, say, you wanted to turn procedural noise into a normal map, ddx & ddy give a simple, if approximate, way to calculate how the noise value is changing in the vicinity of the current fragment, and which way it's sloping, so you can construct an appropriate normal.
Techniques for rendering antialiased lines or intersection highlights may also use screenspace derivatives, to ensure the thickness/falloff is consistent and not dependent on the geometry or view angle. (eg. drawing an antialiased circle / drawing topographical lines on a map or terrain / drawing crisp, vector-like sprites)
In this talk about sand rendering in Journey, the speaker mentions they could have used these derivative functions to control how sparkly the sand is along glancing edges...if they'd known about them at the time (instead they used a mipmapping trick, which under the hood is powered by these kinds of derivatives anyway)
One last note to be aware of: screenspace derivatives can be computed at "coarse"/low precision (meaning one pair of derivatives is shared by the whole quad) or "fine"/high precision (meaning each pixel is compared with only its immediate neighbours in the quad, which could give four distinct derivative pairs over the quad). Coarse is generally plenty, but if you notice you're getting visible 2x2 blocks in your effect, it's a good clue you want to switch to fine/high precision. ;)
(In the diagram at the top I used calculations for fine derivatives, but beware that just ddx/ddy on their own may default to coarse derivatives)
