Why does the order of media queries matter in CSS?

That’s by design of CSS — Cascading Style Sheet.

It means that, if you apply two rules that collide to the same elements, it will choose the last one that was declared, unless the first one has the !important marker or is more specific (e.g. html > body vs just body, the latter is less specific).

So, given this CSS

@media (max-width: 600px) {
  body {
    background: red;
  }
}

@media (max-width: 400px) {
  body {
    background: blue;
  }
}

if the browser window is 350 pixels wide, the background will be blue, while with this CSS

@media (max-width: 400px) {
  body {
    background: blue;
  }
}

@media (max-width: 600px) {
  body {
    background: red;
  }
}

and the same window width, the background will be red. Both rules are indeed matched, but the second one it’s the one that is applied because is the last rule.

Finally, with

@media (max-width: 400px) {
  body {
    background: blue !important;
  }
}

@media (max-width: 600px) {
  body {
    background: red;
  }
}

or

@media (max-width: 400px) {
  html > body {
    background: blue;
  }
}

@media (max-width: 600px) {
  body {
    background: red;
  }
}

the background will be blue (with a 350 pixels wide window).