Using start and end event logs to create a table/view containing spans between the times of each log
Assuming all columns NOT NULL. And 'left' is never earlier than the associated 'joined'.
Simple case
If users can join teams only once (which would be enforced by a UNIQUE constraint on (&quot;user&quot;, team), ideally), then the solution is a simple GROUP BY and works in Redshift as well as in most any RDBMS:
SELECT &quot;user&quot;, team
     , min(CASE WHEN event = 'joined' THEN timestamp END) AS joined
     , max(CASE WHEN event = 'left'   THEN timestamp END) AS &quot;left&quot;
FROM   event
GROUP  BY &quot;user&quot;, team
ORDER  BY &quot;user&quot;, joined NULLS FIRST;

Note the NULLS FIRST clause. Seems like you want to sort an open start with joined IS NULL first. Redshift supports that, too.
Apart from that it's the most basic form of a crosstab / pivot query.
Not so simple
Judging from your column names and example data it might not be so simple. If users can join teams more than once (non-overlapping), you have to do more. You wouldn't want to merge multiple team memberships into one row like in this related answer:

Postgres: Use result of group by as column names

Instead you have to pair up adjacent 'joined' and 'left' rows somehow. There are many ways ...
Postgres 9.4+
For modern Postgres I like this best:
SELECT "user", team
     , min(timestamp) FILTER (WHERE event = 'joined') AS joined
     , max(timestamp) FILTER (WHERE event = 'left'  ) AS "left"
FROM  (
   SELECT *, count(*) FILTER (WHERE event = 'joined')
                      OVER (PARTITION BY "user", team ORDER BY timestamp) AS ct
   FROM   event
   ) sub
GROUP  BY "user", team, ct
ORDER  BY "user", joined NULLS FIRST;
Using the aggregate FILTER clause in a window function as well as in an aggregate function. Related (with links to alternatives):

Return counts for multiple ranges in a single SELECT statement

This way we count how many times the same user joined the same team, so we can group adjacent rows. Works for a missing 'joined' at the begin or a missing 'left' at the end as well.
Redshift
... does not support the new FILTER clause. We can substitute with a plain old CASE:
SELECT &quot;user&quot;, team
     , min(CASE WHEN event = 'joined' THEN timestamp END) AS joined
     , max(CASE WHEN event = 'left'   THEN timestamp END) AS &quot;left&quot;
FROM  (
   SELECT *, count(CASE WHEN event = 'joined' THEN 1 END)
                      OVER (PARTITION BY &quot;user&quot;, team ORDER BY timestamp, event) AS ct
   FROM   event
   ) sub
GROUP  BY &quot;user&quot;, team, ct
ORDER  BY &quot;user&quot;, joined NULLS FIRST;

SQL Fiddle.

Aside: you shouldn't use reserved words as identifiers, even if it's permitted by Redshift (or Postgres).