Custom query in Views? At some point I found the need to modify an SQL query generated by Views, in the end I overrode views_pre_execute and changed the query for that specific view. 

This feels like an ugly hack to me and I wonder if there is a more elegant and maintainable way to do that. Ideal would be a way that would allow me to directly modify the query from the Views UI.
You can also use hook_views_query_alter() to alter the query before it's run. I think this is similar to hook_views_pre_execute, but makes it easier to modify the query. You basically get access to each part of the query through a keyed array. I haven't found much official documentation, but there's a pretty good example of it at https://www.appnovation.com/blog/using-hook-views-query-alter. This is also the approach I had to use to fix a date bug in the Calendar module.