Add subquery with count, having, and orderby
So, from reading your question, it seems you're missing a fundamental step to data modeling with Drupal. The first thing you're going to want to do is to build out content types and fields for your data. This is done through the content types user interface. Once you have all of your data in node content types and fields, it becomes much easier to access this data in views. 

When it comes to creating joins in views, you'll want to use "relationships" under "advanced" on the right hand side of the views UI. Since you're new to Drupal, but not to SQL, you might find it helpful to turn on "Show the SQL Query" here: /admin/structure/views/settings

So, the big realization here is that you don't create custom tables in Drupal databases manually. There are use cases where using a node doesn't make sense. In that case, you'll want to create a custom content entity. This is not done manually in Drupal. While creating entities can be done through code, you're better off using this module to generate your tables:

https://www.drupal.org/project/eck

Finally, most Drupal developers prefer nodes for data storage because Nodes are special entities that have many wonderful features that almost all data in Drupal needs.