Q: Postgresql how to? Given the following tables:
\d users
                                   Table "public.users"
       Column       |            Type             |               Modifiers
--------------------+-----------------------------+----------------------------------------
 id                 | integer                     | not null
 email              | character varying           | not null default ''::character varying
 encrypted_password | character varying           | not null default ''::character varying
 created_at         | timestamp without time zone |
 address_id         | integer                     |

\d address
          Table "public.address"
  Column  |       Type        | Modifiers
----------+-------------------+-----------
 id       | integer           | not null
 street   | character varying |
 city     | character varying |
 province | character varying |
 zip      | character varying |
 country  | character varying |



*

*Write some SQL to select all users who live in XXXX?

*Now scope that query to users created older than 3 months ordered by newest first?

*What index would improve the performance of this query?


A: First query:
select u.id, u.email
from users u
inner join address a
    on u.address_id = a.id
where a.city = 'XXXX'

Second query:
select u.id, u.email
from users u
inner join address a
    on u.address_id = a.id
where a.city = 'XXXX' AND
      u.created_at < NOW() - interval '3 months'
order by u.created_at desc


A: assuming you want to select users who live in a particular city, lets say "Addis Ababa" in a particular country "Ethiopia"
SELECT * FROM "users" AS u JOIN "address" AS a ON a.id = u.address_id WHERE a.city = 'Addis Ababa' AND a.country = 'Ethiopia' AND u.created_at < CURRENT_DATE - INTERVAL '3 months' ORDER BY u.created_at DESC;

