Intuitively, what does it mean that two charts are compatible?

The essential fact: in the intersection of the domains $U\cap V$ both charts will give the same answer to the question "is $f:M\longrightarrow\Bbb R$ differentiable at $x_0\in U\cap V$?"

It means that you can use those two charts to build an atlas. If the charts are not compatible you cannot use them in the same atlas, in this case indeed you would have a transition function not smooth and consequently you could have a function smooth when read in the first chart but not smooth when read in the second one.

Suppose that you are going on a hike that requires many trail maps to cover your whole trip.

Now imagine tracing your path on the maps as you walk. At some point your position will be on two maps at once. The path you trace should be smooth in both maps. If your path is smooth in one map, but has kinks or spontaneously teleports from one point to another the other map, then you know there is something wrong.

Generally, if a smooth path drawn on one map corresponds to a nonsmooth path on the other, then you know there is something wrong with at least one of the maps. Regardless of which map is right or wrong, at the very least these maps are "incompatible" with each other.

Replace "map" with "chart", and you have the intuition for the compatibility condition for charts on a manifold.

Lee's book made it clear for me with the following formula,

$$ \hat{f}_{\phi}:=f \circ \phi^{-1} = f \circ \psi^{-1} \circ \left(\psi \circ \phi^{-1}\right)$$

Here $\hat{f}$ is the coordinate representation of the smooth map $f: M \to N$. Hence, if we know that each $\hat{f}_{\phi}$ is smooth w.r.t each chart $\phi$ then by the observation above, if $ \psi \circ \phi^{-1}$ is too a diffeomorphism then $\hat{f}_{\phi}$ is smooth for any chart. Thus, smoothness is independent of charts. As a consequence we also know that any diffeomorphism $g: M \to N$ gives rise to an isomorphism $D_pg: T_pM \to T_{g(p)}N$ i.e the tangent spaces for the domain and range of $\psi \circ \phi^{-1}$ are isomorphic.

You want the coordinate charts themselves to be $C^\infty$ functions; this condition ensures that without having to first having to define calculus on a $C^\infty$ manifold.

A more direct interpretation of the condition, though, is that calculus with $C^\infty$ things is the same when done in either coordinate chart.

If the transition maps were not smooth, then you could have something that was infinitely differentiable in one set of coordinates not be so in the other set of coordinates.