Q:

Presto/mysql Self Join Table

I have one business table as following shows when people purchase items online.  I want to see 7-day retention rate: on everyday, how many people shop on Day0 come back to shop again during Day1-Day7.  
customer_ID |purchase_date 
1           |2017-01-01       
2           |2017-01-01       
3           |2017-01-01       
2           |2017-01-06       
2           |2017-01-07

Here are my Presto code:
SELECT
    COUNT(DISTINCT bp1.customer_ID) AS retained_customer,
    bp1.purchase_date
FROM
    business bp1,
    business bp2
WHERE
    bp1.customer_ID = bp2.customer_ID
    AND CAST(bp2.purchase_date AS date) BETWEEN date_add('day', 1, CAST(bp1.purchase_date AS date))
    AND date_add('day', 6, CAST(bp1.purchase_date AS date))
GROUP BY
    2
ORDER BY
    2

It runs forever, is there anyone who has a more efficient way to solve this？ 

A:

Not sure what Presto has to do with the query, but here's a query that will provide the information you describe:
SQL Fiddle
MySQL 5.6 Schema Setup:
CREATE TABLE IF NOT EXISTS `business` (
    `id`        INT(11) UNSIGNED        NOT NULL    AUTO_INCREMENT                  COMMENT 'Primary Key',
    `customer_id`       INT(11) UNSIGNED        NULL        DEFAULT 0               COMMENT 'Use for a Foriegn Key or integer value',
    `purchase_date`     TIMESTAMP               NOT NULL    DEFAULT '2017-07-07'    COMMENT '0 or 1 flag',
    PRIMARY KEY (`id`)
) 
    ENGINE=MyISAM 
    AUTO_INCREMENT=1 
    DEFAULT CHARSET=utf8 
    COLLATE=utf8_unicode_ci
    COMMENT '';

INSERT INTO `business`
(`customer_id`,`purchase_date`)
VALUES
(1,'2017-01-01'),
(2,'2017-01-01'),
(3,'2017-01-04'),
(2,'2017-01-06'),
(2,'2017-01-07'),
(3,'2017-01-05'),
(3,'2017-01-06');

Query 1:
SELECT
    Count(DISTINCT b.customer_id) as `NumRetained`,
    CAST(a.purchase_date as DATE) as `Purchase_Date`,
    MIN(b.purchase_date) as `first_purchase`,
    MAX(b.purchase_date) as `last_purchase`
FROM (SELECT 
        d.customer_id, MIN(d.purchase_date) as `purchase_date`
      FROM business d
      GROUP BY d.customer_id
      ) a
LEFT JOIN business b
ON a.customer_id = b.customer_id
    AND CAST(b.purchase_date as DATE) 
      BETWEEN DATE_ADD(CAST(a.purchase_date AS DATE),INTERVAL 1 DAY) AND 
        DATE_ADD(CAST(a.purchase_date AS DATE),INTERVAL 6 DAY)
GROUP BY a.purchase_date
ORDER BY a.purchase_date

Results:
| NumRetained | Purchase_Date |       first_purchase |        last_purchase |
|-------------|---------------|----------------------|----------------------|
|           1 |    2017-01-01 | 2017-01-06T00:00:00Z | 2017-01-07T00:00:00Z |
|           1 |    2017-01-04 | 2017-01-05T00:00:00Z | 2017-01-06T00:00:00Z |

