I'm sure, like many, I've seen both extremes of this. It seems like most developers I've worked with have stopped and generally thought about what they were doing with their queries, but there are those few that leave you bewildered.
1) Developer who produces a software package used by a fair number of counties across the US. They're essentially one of, if not the only, company working in that field. We acted as a go-between between the state and the software company.
There are a set of standard reports included with the software, each of which amounts to little more than a very basic PDF wrapper around an SQL query. A huge, ugly turd of a query. One that they created through some SQL GUI, whenever they created the report.
They hadn't profiled any of them or spent any time tuning them. For a large number of them it took quite a while, even with relatively small numbers of records. The state my employers worked with didn't do things on a small scale. In one query's case it would take upwards of 45 minutes to produce a report under optimal conditions. The indexes were great for the general workload, but lousy for producing the report via that query.
They also clearly hadn't thought about the end user experience. Nothing on the page to indicate progress (hard to demonstrate progress when you're waiting on one monolithic query). It wasn't set up as a nice asynchronous report production either.
While I didn't really have time for it, we wanted to be as helpful to the agency in question as possible, so I took a stab at one report, using a quick python script. Breaking the underlying query apart into a few distinct components, and paying attention to the indexes resulted in 1) producing the report in under a minute. 2) progress bars.
Trying to wrestle that change in approach into the monolithic query was way too complicated for pretty much zero benefit.
2) At another company, a co-worker kept having to reject a deployment request from one particular developer (this was quite a while ago, the company used the waterfall method).
Littered throughout their code was "SELECT * FROM table", followed by implementing all filtering logic inside their code. It got to the point where we celebrated the dev at least putting code forwards that included a WHERE clause: "SELECT * FROM table WHERE", even if we still had to reject it (due to the breaks-on-every-schema-change "SELECT *"). Over and over again we demonstrated that the database server was quicker than his code, and really hoped he'd eventually learn. That was one of the few times I really, really, really wished a team would either use an ORM, or fire the developer.