  Understanding competing charge density wave (CDW) orders in the bilayer
kagome metal ScV$_6$Sn$_6$ remains a challenge. Experimentally, upon cooling,
short-range CDW order with the wave vector
$\mathbf{q}_{2}=(\frac{1}{3},\frac{1}{3},\frac{1}{2})$ is observed, which is
subsequently suppressed by the condensation of the long-range CDW order
$\mathbf{q}_{3}=(\frac{1}{3},\frac{1}{3},\frac{1}{3})$ at lower temperature. In
conflict with these observations, theoretical calculations overestimate the
transition temperature by a few thousand Kelvin and incorrectly predict the
$\mathbf{q}_{2}$ CDW order to be the ground state. Here, we develop a theory of
CDW order in ScV$_6$Sn$_6$ based on anharmonic phonon-phonon calculations
combined with density functional theory. We predict a temperature-driven phase
transition from the high-temperature $\mathbf{q}_{2}$ CDW to the
low-temperature $\mathbf{q}_{3}$ CDW order, fully explaining experimental
observations. Additionally, we clarify that both multi-phonon effects and
semi-core electron states play a crucial role in stabilizing the
$\mathbf{q}_{3}$ order. Finally, we construct a phase diagram of the competing
CDWs in the lattice parameter space and demonstrate that the out-of-plane
lattice parameter plays a pivotal role in manipulating the competing CDWs in
ScV$_6$Sn$_6$, motivating us to propose compressive bi-axial strain as an
experimental protocol to stabilize the $\mathbf{q}_{2}$ CDW order. Our work
provides a full theory of CDWs in ScV$_6$Sn$_6$, rationalizing experimental
observations and resolving ealier discrepancies between theory and experiment.
Overall, our work contributes to the wider effort of understanding CDWs in the
kagome metal family.
