$\def\SL{\mathrm{SL}}\def\GL{\mathrm{GL}}\def\PSL{\mathrm{PSL}}\def\ZZ{\mathbb{Z}}\def\FF{\mathbb{F}}\def\PP{\mathbb{P}}$I previously gave a wrong answer to this question. In fact, such groups exist for $N$ any prime which is $3 \bmod 4$ and in many other cases, as I will now spell out.
If we have $G_1 \subset \SL_2(\ZZ/n_1 \ZZ)$ and $G_2 \subset \SL_2(\ZZ/n_2 \ZZ)$ with $n_1$ and $n_2$ relatively prime, then $G_1 \times G_2 \subset \SL_2(\ZZ/n_1 \ZZ) \times \SL_2(\ZZ/n_2 \ZZ) \cong \SL_2(\ZZ/(n_1 n_2) \ZZ)$ also has this property. So I'll concentrate on prime powers.
I'll concentrate first on the case of $N$ a prime $p$. As in my wrong answer, if $\Gamma \subset \SL_2(\FF_p)$ has the required property, so does any larger group containing $\Gamma$, so it is enough to restrict our attention to the maximal proper subgroups of $\SL_2(\FF_p)$. These are classified; see Corollary 2.2 in this paper by King. Note that types (d) and (e) only occur for $p$ a prime power, not a prime, so they are irrelevant to us. Group (a) has a two element orbit and group (c) is $\Gamma_0(p)$, with two cusps. This leaves (b) and (f), (g), (h).
Type (b) This is the one I got wrong; I misread King's paper. The correct description of Group (b) is as follows: Let $q$ be an isotropic quadratic form on $\FF_p^2$. For example, if $p \equiv 3 \bmod 4$, then
we can take $q(x,y) = x^2+y^2$. Then Group (b) is the group of matrices which either preserve the quadratic form or multiply it by $-1$. (I misread this as the index $2$ subgroup that preserve the form.) For example, when $q(x,y) = x^2+y^2$, this is matrices of the form $\left[ \begin{smallmatrix} a & b \\ -b & a \end{smallmatrix} \right]$ with $a^2+b^2 = 1$ or $\left[ \begin{smallmatrix} c & d \\ d & -c \end{smallmatrix} \right]$ with $c^2+d^2 = -1$.
Now, $q$ descends to a map $\mathbb{P}^1(\FF_p) \to \FF_p^{\times}/(\FF_p^{\times})^2$. The two classes in $\FF_p^{\times}/(\FF_p^{\times})^2$ thus divide $\PP^1(\FF_p)$ into two orbits for the action of the index two subgroup that preserves $q$. What about the elements that switch the sign of $q$? If $-1$ is a square, they preserve the split of $\PP^1(\FF_p)$ into two orbits; but, if $-1$ is not square, then they switch the orbits, and we get just one cusp. Of course, $-1$ is not square if and only if $p \equiv 3 \bmod 4$. Conveniently, in this case, we can take $q(x,y) = x^2+y^2$, so all of my examples are relevant.
Lifting to $\ZZ/p^k \ZZ$ Take an isotropic quadratic form on $\FF_p^2$ and lift it to an quadratic form $Q: \ZZ_p^2 \to \ZZ_p$ where $\ZZ_p$ is the $p$-adics. Let $\Sigma \subset \SL_2(\ZZ_p)$ be the matrices which rescale $Q$ by $\pm 1$. Then $Q$ descends to a surjection $\PP^1(\ZZ_p) \to \ZZ_p^{\times} / (\ZZ_p^{\times})^2$ (here $\ZZ_p^{\times}$ is the unit group of $\ZZ_p$ and $\PP^1(\ZZ_p)$ is pairs $[u:v]$ in $\ZZ_p^2$, not both in $p \ZZ_p$, modulo simultaneous rescaling by $\ZZ_p^{\times}$). The group $\Sigma$ acts transitively on $\PP^1(\ZZ_p)$; if $-1$ generates the quotient $\ZZ_p^{\times} / (\ZZ_p^{\times})^2$, which happens for $p \equiv 3 \bmod 4$. Descending modulo $p^k$ gives us examples in $\ZZ/p^k \ZZ$.
Types (f), (g), (h) These types arise from the exceptional subgroups $A_4$, $S_4$ and $A_5$ of $\PSL_2(\FF_p)$ (for $p \geq 5$). They can only act transitively on $\PP^1(\FF_p)$ if $p+1$ divides the order of these groups, which happens for $p \in \{ 5,11 \}$, $p \in \{ 5,7,11,23 \}$ and $p \in \{ 5,11,19,29 \}$ rspectively. I believe all of those cases occur; many of them occur in the list of near fields. I am not sure which of these cases can be lifted modulo powers of primes.
