Chemistry - Why do the alkali metals form different products upon combustion in air?
Since I will deal with all of the alkali metals in this answer, I think the question should also be broadened. There is no point in covering one single metal (sodium) without touching the others since it is the trend going down the group that we are interested in.
All thermodynamic data is taken from Prof. M. Hayward's lecture notes at Oxford.
So, firstly, some data. There is an increase in ionic radius going down the group, which should not be surprising:
$$\begin{array}{cc} \hline \ce{M} & \text{Ionic radius of }\ce{M+}\text{ / pm} \\ \hline \ce{Li} & 76 \\ \ce{Na} & 102 \\ \ce{K} & 138 \\ \ce{Rb} & 152 \\ \ce{Cs} & 167 \\ \hline \end{array}$$
This leads to an decrease in the magnitude of the lattice enthalpies of the Group I superoxides, peroxides, and oxides, going down the group:
$$\begin{array}{cccc} \hline \ce{M} & \Delta H_\mathrm{L}(\ce{MO2})\mathrm{~/~kJ~mol^{-1}} & \Delta H_\mathrm{L}(\ce{M2O2})\mathrm{~/~kJ~mol^{-1}} & \Delta H_\mathrm{L}(\ce{M2O})\mathrm{~/~kJ~mol^{-1}} \\ \hline \ce{Li} & -960 & -2748 & -3295 \\ \ce{Na} & -860 & -2475 & -2909 \\ \ce{K} & -752 & -2175 & -2503 \\ \ce{Rb} & -717 & -2077 & -2375 \\ \ce{Cs} & -683 & -1981 & -2250 \\ \hline \end{array}$$
As for why there is an increase in magnitude going across the table, we have to look at the factors controlling the magnitude of the lattice enthalpy:
$$\Delta H_\mathrm{L} \propto \frac{\nu z_+ z_-}{r_+ + r_-} \tag{1}$$
where $\nu$ is the number of ions in one formula unit, $z_+$ and $z_-$ are the charge numbers on the cation and anion, and $r_+$ and $r_-$ are the ionic radii:
$$\begin{array}{ccccc} \hline \text{Formula unit} & \nu & z_+ & z_- & \text{Numerator in eq. (1)} & r_-\text{ / pm} \\ \hline \ce{MO2} & 2 & 1 & 1 & 2 & 149 \\ \ce{M2O2} & 3 & 1 & 2 & 6 & 159 \\ \ce{M2O} & 3 & 1 & 2 & 6 & 120 \\ \hline \end{array}$$
The lattice energies of the peroxides and oxides are roughly 3 times those of the corresponding superoxides, because of the larger numerator. The lattice energies of the oxides have a slightly larger magnitude than those of the corresponding peroxides, because of the smaller anionic radius.
Just looking at the lattice enthalpies, we might think that all metal cations would form the oxides. However, this approach is flawed because it does not take into consideration the energy cost of forming the three different anions from molecular dioxygen. Recall that the lattice enthalpy is defined as $\Delta H$ for the reaction
$$\ce{M+ (g) + X- (g) -> MX (s)}$$
However when we burn a metal in oxygen we are starting from $\ce{M}$ and $\ce{O2}$. So, we have to figure out the energy needed to get from $\ce{M}$ to $\ce{M+}$, and from $\ce{O2}$ to the relevant anion ($\ce{O2-}$, $\ce{O2^2-}$, or $\ce{O^2-}$).
The analysis here is complicated by the fact that the charges on the anions are not the same. Therefore, the reaction
$$\ce{M(s) + O2 (g) -> MO2(s)}$$
cannot be directly compared with
$$\ce{2M(s) + O2(g) -> M2O2(s)}$$
In order to "standardise" the equations, we will consider the reactions per mole of metal. One can loosely think of the combustion reaction as "releasing" some kind of energy within the metal; the most favourable reaction will be that which "releases" the most energy per mole of metal. Therefore, the three reactions we are considering are:
$$\begin{align} \ce{M (s) + O2 (g) &-> MO2 (s)} & \Delta H_1 \\ \ce{M (s) + 1/2 O2 (g) &-> 1/2 M2O2 (s)} & \Delta H_2 \\ \ce{M (s) + 1/4 O2 (g) &-> 1/2 M2O (s)} & \Delta H_3 \\ \end{align}$$
Now, we construct Hess cycles for all three reactions.
Superoxides $$\require{AMScd}\begin{CD} \color{blue}{\ce{M(s) + O2(g)}} @>{\Large\color{blue}{\Delta H_1}}>> \color{blue}{\ce{MO2(s)}} \\ @V{\Large\Delta H_\mathrm{f}(\ce{M+})}VV @AA{\Large\Delta H_\mathrm{L}(\ce{MO2})}A \\ \ce{M+(g) + e- + O2(g)} @>>{\Large\Delta H_\mathrm{f}(\ce{O2^-})}> \ce{M+(g) + O2^-(g)} \end{CD}$$
Peroxides $$\require{AMScd}\begin{CD} \color{blue}{\ce{M(s) + 1/2O2(g)}} @>{\Large\color{blue}{\Delta H_2}}>> \color{blue}{\ce{1/2M2O2(s)}} \\ @V{\Large\Delta H_\mathrm{f}(\ce{M+})}VV @AA{\Large\ce{1/2}\Delta H_\mathrm{L}(\ce{M2O2})}A \\ \ce{M+(g) + e- + 1/2O2(g)} @>>{\Large\ce{1/2}\Delta H_\mathrm{f}(\ce{O2^2-})}> \ce{M+(g) + 1/2O2^2-(g)} \end{CD}$$
Oxides $$\require{AMScd}\begin{CD} \color{blue}{\ce{M(s) + 1/4O2(g)}} @>{\Large\color{blue}{\Delta H_3}}>> \color{blue}{\ce{1/2M2O(s)}} \\ @V{\Large\Delta H_\mathrm{f}(\ce{M+})}VV @AA{\Large\ce{1/2}\Delta H_\mathrm{L}(\ce{M2O})}A \\ \ce{M+(g) + e- + 1/4O2(g)} @>>{\Large\ce{1/2}\Delta H_\mathrm{f}(\ce{O^2-})}> \ce{M+(g) + 1/2O^2-(g)} \end{CD}$$
Now we need more data. $\Delta H_\mathrm{f}(\ce{M+})$ is simply the sum of the atomisation energy and first ionisation energy:
$$\begin{array}{cc} \hline \ce{M} & \Delta H_\mathrm{f}(\ce{M+})\text{ / }\mathrm{kJ~mol^{-1}} \\ \hline \ce{Li} & 679 \\ \ce{Na} & 603 \\ \ce{K} & 508 \\ \ce{Rb} & 484 \\ \ce{Cs} & 472 \\ \hline \end{array}$$
and the enthalpies of formation of the anions are
$$\begin{array}{cc} \hline \ce{X} & \Delta H_\mathrm{f}(\ce{X})\text{ / }\mathrm{kJ~mol^{-1}} \\ \hline \ce{O2-} & -105 \\ \ce{O2^2-} & +520 \\ \ce{O^2-} & +1020 \\ \hline \end{array}$$
Why is the enthalpy of formation of $\ce{O^2-}$ so large? The answer is that, to get from $\ce{1/2 O2}$ to $\ce{O^2-}$, you need to first break the $\ce{O=O}$ bond, then add two electrons to the oxygen. Furthermore, the second electron affinity is often an unfavourable process. For the other two anions, you don't need to break the $\ce{O=O}$ bond.
So now, we can see a trend at work already. Going from the superoxides to peroxides to oxides, the more negative lattice enthalpy favours the formation of the oxide. However, the increasing heat of formation of the anion favours the formation of the superoxide.
When do each of these two factors win out? Well, when lattice enthalpies are comparatively large, we would expect the lattice enthalpy factor to outweigh the heat of formation of the anion. Lattice enthalpies are large precisely when the cation is small, and therefore lithium forms the oxide when heated in oxygen. However, with caesium, lattice enthalpies are smaller, less significant, and the heat of formation of the anion wins out; caesium therefore forms the superoxide.
The trend is of course not black and white. Going down the group from lithium to caesium, we might guess that perhaps there are one or two elements that form the intermediate peroxide. That element is sodium. You could say that the larger lattice energies of sodium salts sufficiently compensate for the formation of the peroxide ion, but aren't enough to compensate for the formation of the oxide ion.
I leave you with the last bunch of numbers, which tabulate the values of $\Delta H_1$ through $\Delta H_3$ for all the elements (all values in $\mathrm{kJ~mol^{-1}}$). You can actually calculate these yourself by plugging the data above into the Hess cycles. It seems that the data is a little different from that given in the Chemguide screenshot you have, but the conclusion is the same, so I'll ignore that:
$$\begin{array}{cccc} \hline \ce{M} & \Delta H_1\text{ (superoxide)} & \Delta H_2\text{ (peroxide)} & \Delta H_3\text{ (oxide)} \\ \hline \ce{Li} & -386 & -435 & \mathbf{-459} \\ \ce{Na} & -362 & \mathbf{-375} & -342 \\ \ce{K} & \mathbf{-349} & -320 & -234 \\ \ce{Rb} & \mathbf{-338} & -295 & -194 \\ \ce{Cs} & \mathbf{-316} & -259 & -143 \\ \hline \end{array}$$
As described earlier, the salt with the most negative enthalpy of formation will be preferentially formed. These are bolded in the table.