0001 SELECT
0002 s_store_name,
0003 s_company_id,
0004 s_street_number,
0005 s_street_name,
0006 s_street_type,
0007 s_suite_number,
0008 s_city,
0009 s_county,
0010 s_state,
0011 s_zip,
0012 sum(CASE WHEN (sr_returned_date_sk - ss_sold_date_sk <= 30)
0013 THEN 1
0014 ELSE 0 END) AS `30 days `,
0015 sum(CASE WHEN (sr_returned_date_sk - ss_sold_date_sk > 30) AND
0016 (sr_returned_date_sk - ss_sold_date_sk <= 60)
0017 THEN 1
0018 ELSE 0 END) AS `31 - 60 days `,
0019 sum(CASE WHEN (sr_returned_date_sk - ss_sold_date_sk > 60) AND
0020 (sr_returned_date_sk - ss_sold_date_sk <= 90)
0021 THEN 1
0022 ELSE 0 END) AS `61 - 90 days `,
0023 sum(CASE WHEN (sr_returned_date_sk - ss_sold_date_sk > 90) AND
0024 (sr_returned_date_sk - ss_sold_date_sk <= 120)
0025 THEN 1
0026 ELSE 0 END) AS `91 - 120 days `,
0027 sum(CASE WHEN (sr_returned_date_sk - ss_sold_date_sk > 120)
0028 THEN 1
0029 ELSE 0 END) AS `>120 days `
0030 FROM
0031 store_sales, store_returns, store, date_dim d1, date_dim d2
0032 WHERE
0033 d2.d_year = 2001
0034 AND d2.d_moy = 8
0035 AND ss_ticket_number = sr_ticket_number
0036 AND ss_item_sk = sr_item_sk
0037 AND ss_sold_date_sk = d1.d_date_sk
0038 AND sr_returned_date_sk = d2.d_date_sk
0039 AND ss_customer_sk = sr_customer_sk
0040 AND ss_store_sk = s_store_sk
0041 GROUP BY
0042 s_store_name, s_company_id, s_street_number, s_street_name, s_street_type,
0043 s_suite_number, s_city, s_county, s_state, s_zip
0044 ORDER BY
0045 s_store_name, s_company_id, s_street_number, s_street_name, s_street_type,
0046 s_suite_number, s_city, s_county, s_state, s_zip
0047 LIMIT 100
