Weird misbehaviour of my PC, which left me boggled...

Since it worked on the other PC, it would be helpful to have a HW and OS list, maybe even DXDIAG of both PCs.

Similar thing happened to me few years ago. when i checked the task manager there was some unknown background programs taking to much cpu and hard disk. An old friend who was a software engineer detected a crypto malware using my pc resources for mining. He switch the hdd and then everything was back to normal. In my experience all these freezes/black screen/blue screen of death are 80% caused by faulty ram/gpu or some malware, trojan eating pc resources..

Try another PSU?

disconnect all my other HD, and only then I was able to install Windows from scratch.

Test all your HDDs and check for their SMART values. The freezes are typical of defective hard drives.

Free software to check SMART: https://crystalmark.info/en/software/crystaldiskinfo/

Firstly like @orono mentioned, check your disks' SMART data. If that's not it, my second guess would be hardware acceleration. Check if it's enabled for Steam. If so, disable it. Less likely but it could also because of RAMs, either use memtest86+ or try unplugging them one by one to see if one of them is defective.

If you're using an amd processor, try setting a fixed processor clock speed and voltage to the base clock speed for your cpu in your bios and see if it still happens. That fixed a similar black screen data corruption problem for me. For me the voltage needed to be 1.2 and no boosting or the data corrupting crashes happen. Then use sfc /scannow in a command prompt set to administrator mode to fix the data corruption. Before doing this I replaced everything except cpu motherboard power supply and it was still happening..I only knew to try this because I ran HWinfo after putting all the old components back and noticed that my processor would boost to 5050mhz before a crash when the default boost was supposed to be lower than that.

Eventually I needed to delete the entire partition, disconnect all my other HD, and only then I was able to install Windows from scratch.

That is usually a sign of the PSU failing to deliver enough or stable current to the system. As you decrease the load on the PSU by disconnecting parts of the computer (HDDs, GPU, fans,...), the system gets more stable.

It could also be the MB, small things like a failing capacitor can lead to random behaviors like the ones you describe.

I'd rule out the GPU since it crashed on "simple games" but worked fine with intensive-gfx games like Hogwarts Legacy.

If you are confident with it, you could try to boot from a Linux distro and run some diagnostics. Ubuntu includes memtest86 as a boot option, so you can start by testing the memory. There are also many stress tools to check the CPU and GPU. as well as the hard disks. It will also give you better logs of what's happening, unless it's a total freeze where the system hangs and becomes unresponsive.

As you could run Hogwarts without a crash I'd rule out GPU. My money is on RAM or disk failure. Try to run your pc with one RAM stick only in different RAM slots and see if the pc crashes. Do this with each stick to eliminate this possible point of failure. Check S.M.A.R.T. values of your storage drives with eg. HWiNFO64. Run memtest to check RAM reliability. In motherboard BIOS/UEFI change all settings to default. Check if your CPU is seated in socket correctly and check for overheating. If available try another PSU.

I hope you understand that I suggest to do those steps one by one not all at once.

Your motherboard could have crapped out but I never experienced it myself and from what I know it's rather rare.

Edit: generally it's always helpful to provide us with a short summary of specs. If you're running a 4090 with a 500 Watts PSU we could pinpoint to issue immediately.

First, make sure that you are not overclocking any part of your system before declaring any hardware at fault. Second, this may seem obvious but its worth asking to rule out: are you actually connecting your monitor to the GPU video ports rather than the motherboard on-board video port? If you are using the motherboard ports then switch to the HDMI ports on the GPU itself. If you were using the proper GPU ports, then I would suggest testing the power supply as others have mentioned. If your power supply is insufficient, or failing, it would explain why you have problems with different GPUs and why those GPUs work fine in a different PC with a working PSU. Swapping PSU can be kind of annoying, but pulling the power supply from the working PC and just temporarily hooking it up to the one that has issues will allow you to determine if it is actually the PSU or the motherboard.

GTX 1070, which is much weaker

still eats 145 watts, still the most of any single component

everything else works fine.

attempted a full cpu load test?

last time I swapped something was a couple of months ago, when I got a new PSU.

That alone makes it the prime target. Weird symptoms as well. Can always break even if new. Can check voltages but they might only spike erratically. What happened with the previous one?

My rec also is to check with a different PSU.

I was going to be leaving a positive review... until it broke my PC. Twice.

no, stop. Software is incapable of breaking hardware. It can trigger the failure, but hw was broken at that point already.

Memtest

As someone who spent 6 years repairing this stuff, this looks a LOT like a RAM issue, where most likely one of the sticks is failing. As was already mentioned above, you can use memtest86 utility to test the memory sticks, either both or separately, for errors

I once had something similar aka undefined crashes without any apparent reason.
Check your SATA cables if they are properly plugged in on both ends.

A broken motherboard, it's basically the brains of a computer (with it's CPU), and when you get faulty brains it can send all sorts of wrong signals to other body parts, making them not work anymore but it can also be a bodypart itself. I had that happen before (but been 20 years ago and i always had my dads pc to try and replace things) but it was hard to figure out it was the motherboard.

There is no hurt in trying to replace the PSU or a motherboard if you got a spare one near you, granted with old computers that was much easier to do then new computers, i never had problems fixing things, but i wouldn't have a clue how to disassemble mine now, it's all so built in (prebuild) and my case weighs 25kg and no drivers license for a repair shop AND no replacement parts anymore at hand, so i have to knock on wood there.

Have you looked at the event viewer after these crashes? There were a couple of infamous instances where GPU timings (or something similar) were the culprit, usually related to specific versions of GPU driver.
Check the event viewer and see what exactly the error/issue is when system is frozen.

Have you checked the ram? Maybe the problem is there.

"I swapped something was a couple of months ago, when I got a new PSU" well, an obvious suspect is there

Long ago I kept the PC of a friend in shape whenever it misbehaved. Once we got a similar problem. Tried different approaches and after several failed attempts we ended replacing the motherboard and that was the issue. New motherboard, no issues. We would have returned the new motherboard if it didn't help, ofc.

Check visually all capacitors (well I think that's the name of the cylindrical things on the MB) to see if any is strangely swollen. That's a sign that it's the MB.

That's just one experience, which might or might not be the same as your problem. But if you have a nearby good shop with good prices and good return policy, you can try several things "for free".

Good luck!

For me hard to give help to others as it's always almost a blank(in my brain) for me but when it comes to my system I always know/understand what could be the issue etc.. Not really sure what could I suggest if you had blue screen it would be simpler but you could check it if you have a "dump", though by default it's off I think(doesn't save). But it could help diagnose some things as using google it could say it's this or hardware issue.. If you want I could at least write a small guide how to get that functionality and where to check if you had blue screen of death "dump" when you have a black screen/freeze...
And also "fun" story from me about a problem I had... I had occasional BSOD and didn't really know the reason... I reinstalled windows 3 times and still the same issue... Then decided to check what's up (using bsod dumps and a program to read it etc.) and turns out the problem was a ds4windows driver(used that program for dualshock controller and somehow that driver stayed for some reason when reinstalled windows(with formatting the drive))...Reinstalled that one(driver) and no problem since.... Except dead ram but at least that was an upgrade to 32gb :)

Loose sata cable?

(Reading your last edit) Sure sounds like it could have been just a loose connection someplace. I think, that temperature changes alone can lead to this at times, especially if it wasn't too well connected to start with. Lucky you, if it stays this way :)

Loose connector or a connection compromised by dust.

"It started when it started to get black screen + freeze during games.
It was almost random...
Some games it ran fine. While others got a black screen. (My monitor shows "No Signal" message)"

I had the same thing and I was NOT able to REALLY pinpoint the source with trial and error by using multiple PC parts.

I basically pick apart 3 PCs to test this issue. I checked everything and did clean my PC and all parts. I was thinking it is the graphic card problem as it was getting hot in some games so I put my PC into a bigger case with better airflow and more computer fans and tested and it was fine on other motherboards.

In the end I did replace the motherboard for a different one and it looks like this fixed the problem.

What is strange is that the motherboard was not broken in any way and is now sitting in my father PC working just fine and since he is not a gamer the problems I experienced never happened to him.

I think some motherboards + graphic cards configuration have this strange problem with communicating with each other or something BUT I can't be sure.