title: How to design this table schema (and how to query it)
body: <p>I have a table that stores a list of members - for the sake of simplicity, I will use a simple real-world case that models my use case.</p>
  
  <p>Let's use the analogy of a sports club or gym.</p>
  
  <p>The membership of the gym changes every three months (for example) - with some old members leaving, some new members joining and some members staying unchanged.</p>
  
  <p>I want to run a query on the table - spanning a multi-time period and return the average weight of all of the members in the club.</p>
  
  <p>These are the tables I have come up with so far:</p>
  
  <pre><code>-- A table containing all members the gym has ever had
  -- current members have their leave_date field left at NULL
  -- departed members have their leave_date field set to the days they left the gym
  CREATE TABLE IF NOT EXISTS member (
                     id              PRIMARY KEY NOT NULL,
                     name            TEXT NOT NULL,
                     join_date       DATE NOT NULL,
  
                     -- set to NULL if user has not left yet
                     leave_date      DATE DEFAULT NULL
          );
  
  
  -- A table of members weights. 
  -- This table is populated DAILY,after the weights of CURRENT members
  -- has been recorded
  CREATE TABLE IF NOT EXISTS current_member_weight (
                     id              PRIMARY KEY NOT NULL,
                     calendar_date   DATE NOT NULL,
                     member_id       INTEGER REFERENCES member(id) NOT NULL,
                     weight          REAL NOT NULL
         );
  
  
  -- I want to write a query that returns the AVERAGE daily weight of
  -- CURRENT members of the gym. The query should take a starting_date
  -- and an ending_date between which to calculate the daily 
  -- averages. The aver
  
  -- PSEUDO SQL BELOW!
  SELECT calendar_date, AVG(weight) 
  FROM member, current_member_weight
  WHERE calendar_date BETWEEN(starting_date, ending_date);
  </code></pre>
  
  <p>I have two questions:</p>
  
  <ol>
  <li>can the schema above be improved - if yes, please illustrate</li>
  <li>How can I write an SQL* query to return the average weights calculated for all members in the gym during a specified period (t1, t2), where (t1,t2) spans a period that members have joined/left the gym?</li>
  </ol>
  
  <p><strong>[[Note about SQL]]</strong></p>
  
  <p>Preferably, any SQL shown would be database anagnostic, however if a particular flavour of SQL is to be used, I'd prefer PostgreSQL, since that this is the database I'm using.</p>