title: I want to summarize http status by date with amazon athena
body: <p>I have access logs in Athena like this.</p>
  <pre><code>time                  hostname    user_agent      http_status  col_0    col_1     col_2    col_3
  2018-06-01T10:00:00   host01      Mozilla/5.0     200          2018     6         1        10
  2019-06-01T10:00:00   host01      Mozilla/5.0     200          2019     6         1        10
  2020-06-01T10:00:00   host01      Mozilla/5.0     404          2020     6         1        10
  2021-06-08T10:00:00   host01      Mozilla/5.0     404          2021     6         8        10
  2021-06-09T10:00:00   host01      Mozilla/5.0     200          2021     6         9        10
  2021-06-10T10:00:00   host01      Mozilla/5.0     404          2021     6         10        10
  2021-06-10T11:00:00   host01      Mozilla/5.0     503          2021     6         10        11
  2021-06-11T10:00:00   host01      Mozilla/5.0     200          2021     6         11        10
  2021-06-12T10:00:00   host01      Mozilla/5.0     503          2021     6         12        10
  2021-06-13T10:00:00   host01      Mozilla/5.0     200          2021     6         13        10
  2021-06-14T10:00:00   host01      Mozilla/5.0     404          2021     6         14        10
  2021-06-15T10:00:00   host01      Mozilla/5.0     200          2021     6         15        10
  2021-06-15T11:00:00   host01      Mozilla/5.0     503          2021     6         15        11
  </code></pre>
  <p>I would like to count the http status by date.
  At that time, it is necessary to display the data for one week from now.</p>
  <pre><code>date        status_40x  status_50x
  2021-06-09  0           0
  2021-06-10  1           1
  2021-06-11  0           0
  2021-06-12  0           1
  2021-06-13  0           0
  2021-06-14  1           0
  2021-06-15  0           1
  </code></pre>
  <p>How do I make a sql query?</p>
  <p>The types are ...</p>
  <pre><code>time (string) 
  hostname (string) 
  user_agent (string) 
  http_status (string) 
  col_0 (string)(Partitioned) 
  col_1 (string)(Partitioned) 
  col_2 (string)(Partitioned) 
  col_3 (string)(Partitioned)
  </code></pre>
  <p><strong>query</strong></p>
  <pre><code>select d.dte, coalesce(status_404, 0), coalesce(status_503, 0)
  from (select date '2021-06-09' + n.n * interval '1' day as dte
        from unnest(sequence(0, 6, 1)) as n(n)
       ) d left join
       (select DATE_TRUNC('DAY', cast(from_iso8601_timestamp(time) as date)) as dte, 
               count_if(http_status = '404') AS status_404,
               count_if(http_status = '503') AS status_503
        from access_logs al
        group by dte
       ) al
       on al.dte = d.dte
  </code></pre>
  <p><strong>error</strong></p>
  <pre><code>Error running query: SYNTAX_ERROR: line 9:16: Column 'dte' cannot be resolved
  </code></pre>