• Home
• com.alibaba
• druid
• 1.2.21
• source code
• query01.sql

×

Project download

Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $

You can buy this project and download/modify it how often you want.

tpcds.query01.sql Maven / Gradle / Ivy

WITH customer_total_return AS (SELECT sr_customer_sk     AS ctr_customer_sk,
                                      sr_store_sk        AS ctr_store_sk,
                                      sum(SR_RETURN_AMT) AS ctr_total_return
                               FROM store_returns,
                                    date_dim
                               WHERE sr_returned_date_sk = d_date_sk
                                 AND d_year = 2000
                               GROUP BY sr_customer_sk, sr_store_sk)
SELECT c_customer_id
FROM customer_total_return ctr1,
     store,
     customer
WHERE ctr1.ctr_total_return > (SELECT avg(ctr_total_return) * 1.2
                               FROM customer_total_return ctr2
                               WHERE ctr1.ctr_store_sk = ctr2.ctr_store_sk)
  AND s_store_sk = ctr1.ctr_store_sk
  AND s_state = 'TN'
  AND ctr1.ctr_customer_sk = c_customer_sk
ORDER BY c_customer_id LIMIT 100