So eg in this Khan Academy video Sal proves that A.T@A makes an invertible (non-singular) matrix.
But eg if A = [[1,2,3],[4,5,6],[7,8,9]], that's not the case? Why is that? What am I missing?
Additional question: I noticed if I do B = A.T@A and then C = B.T@B, then C is indeed invertible! Is that a coincidence or is there some mathematical basis for why double transpose works when singular doesn't?
Note: practitioner here, not mathematician, so apologies if this is stupid. Also would appreciate an answer without long proofs:)
That video shows that if $A$ has linearly independent columns then $A^T A$ is invertible. But your matrix $A$ does not have linearly independent columns.
A square matrix is invertible if and only if its null space is trivial (that is, contains only the zero vector). Also, the following fact is helpful to know: if $A$ is a real $m \times n$ matrix, then $A$ and $B = A^T A$ have the same null space. (Proof: $Ax = 0 \implies A^T A x = 0 \implies x^T A^T Ax = 0 \implies \| Ax \|^2 = 0 \implies Ax = 0$.)
So if $A$ has a nontrivial null space, then so does $A^T A$. So in that case $A^T A$ is not invertible.
By the way, notice that $A$ has the same null space as $B$, which has the same null space as $B^T B$. So, if $A$ is not invertible, then neither is $B^T B$. This contradicts the statement you made in your second question. Your matrix $C$ is not invertible.