hook_query_alter can be used to effectively change or rewrite many of Drupal's system queries, like those generated from views or modules that use the appropriate query building APIs. This can be useful for patching or optimizing queries across a site.
In the third series of this guide we'll go over how to collect user generated content for the site to produce photo galleries, videos, events, blog posts, etc.
In the second series of this guide we'll go over how to create highly configurable, clean user profiles in drupal 6 using the content profile module.
In the first series of this guide we'll go over the modules needed to build a social network with user contributed content like wikiplomacy.com or ygrin.com using Drupal 6.