1 
    **table name:order_details                               table name:customer_details**

    email         | order_id                         email         | customer_reg_date
    ------------   ----------                       -----------      -----------------
    [email protected]   201                              [email protected]       2019-05-24
    [email protected]   202                              [email protected]       2019-07-15
    [email protected]   203                              [email protected]       2019-03-29
    [email protected]   204
    [email protected]   205

    **Result**
    email   ||   average_weeks_per_order
    ------       -----------------------
    [email protected]     9

    1.count the number of order for each email
    2.number of days between customer_reg_date and the current day then subtract with 7.
    3.subtract s-2 with number of orders in s-1
    Example:-
    [email protected]
    number of orders-2
    (current_date - '2019-05-24')/7=18
    18/2=9
    ans:9
    I mean,
    (current_day-customer_reg_date/7)/number of order-This shoud be calculated for each customer.

    I tried the following code but results are not efficient and getting more emails

    QUERY:select distinct a.email,
       (((current_date -b.created_on)/7)/(count(distinct(a.order_id))))as average_weeks_perorder
       FROM ord_details a,cus_details b
       where a.email not in(select email from cus_details where customer_groups='internal')
       GROUP by a.email,b.created_on;

    SCREENSHOT:

    [enter image description here][1]
    [email protected]   0
    [email protected]   1
    [email protected]  2
    [email protected]  3
    [email protected]  4
    [email protected]  5
    [email protected]  6
    [email protected]  7
    [email protected]  8
    [email protected]  9
    [email protected]  10
    [email protected]  11
    [email protected]  12
    [email protected]  13
    [email protected]  14
    [email protected]  15
    [email protected]  16
    [email protected]  17
    [email protected]  18
    [email protected]  19
    [email protected]  20
    [email protected]  21
    [email protected]  22
    [email protected]  23
    [email protected]  24
    [email protected]  25
    [email protected]  26
    [email protected]  27
    [email protected]  28
    [email protected]  32
    [email protected]   0
    [email protected]   1
    [email protected]   2
    [email protected]   3
    [email protected]   4
    [email protected]   5
    [email protected]   6
    [email protected]  2
    [email protected]  3
    [email protected]  4
    [email protected]  5
    [email protected]  6
    [email protected]  7
    [email protected]  8
    [email protected]  9
    [email protected]  10
    [email protected]  11
    [email protected]  12
    [email protected]  13
    [email protected]  14
    [email protected]  15
    [email protected]  16
    [email protected]  17
    [email protected]  18
    [email protected]  19
    [email protected]  20
    [email protected]  21
    [email protected]  22
    [email protected]  23
    [email protected]  24
    [email protected]  25
    [email protected]  26
    [email protected]  27
    [email protected]  28
    [email protected]  32
    [email protected]   0
    [email protected]   1
    [email protected]   2
    [email protected]   3
    [email protected]   4
    [email protected]   5
    [email protected]   6
    [email protected]  2
    [email protected]  3
    [email protected]  4
    [email protected]  5
    [email protected]  6
    [email protected]  7
    [email protected]  8
    [email protected]  9
    [email protected]  10
    [email protected]  11
    [email protected]  12
    [email protected]  13
    [email protected]  14
    [email protected]  15
    [email protected]  16
    [email protected]  17
    [email protected]  18
    [email protected]  19
    [email protected]  20
    [email protected]  21
    [email protected]  22
    [email protected]  23
    [email protected]  24
    [email protected]  25
    [email protected]  26
    [email protected]  27
    [email protected]  28
    [email protected]  32
    [email protected]   0
    [email protected]   1
    [email protected]   2
    [email protected]   3
    [email protected]   4
    [email protected] 2
    [email protected] 3
    [email protected] 4
    [email protected] 5
    [email protected] 6
    [email protected] 7
    [email protected] 8
    [email protected] 9
    [email protected] 10
    [email protected] 11
    [email protected] 12
    [email protected] 13
    [email protected] 14
    [email protected] 15
    [email protected] 16
    [email protected] 17
    [email protected] 18
    [email protected] 19
    [email protected] 20
    [email protected] 21
    [email protected] 22
    [email protected] 23
    [email protected] 24
    [email protected] 25
    [email protected] 26
    [email protected] 27
    [email protected] 28
    [email protected] 32
    [email protected]   2
    [email protected]   3
    [email protected]   4
    [email protected]   5
    [email protected]   6
    [email protected]   7
    [email protected]   8
    [email protected]   9
    [email protected]   10
    [email protected]   11
    [email protected]   12
    [email protected]   13
    [email protected]   14
    [email protected]   15
    [email protected]   16
    [email protected]   17
    [email protected]   18
    [email protected]   19
    [email protected]   20
    [email protected]   21
    [email protected]   22
    [email protected]   23
    [email protected]   24
    [email protected]   25
    [email protected]   26
    [email protected]   27
    [email protected]   28
    [email protected]   32
    [email protected]   2
    [email protected]   3
    [email protected]   4
    [email protected]   5
    [email protected]   6
    [email protected]   7
    [email protected]   8
    [email protected]   9
    [email protected]   10
    [email protected]   11
    [email protected]   12
    [email protected]   13
    [email protected]   14
    [email protected]   15
    [email protected]   16
    [email protected]   17
    [email protected]   18
    [email protected]   19
    [email protected]   20
    [email protected]   21
    [email protected]   22
    [email protected]   23
    [email protected]   24
    [email protected]   25
    [email protected]   26
    [email protected]   27
    [email protected]   28
    [email protected]   32
    [email protected]   0
    [email protected]   1
    [email protected]   2
    [email protected]   3
    [email protected]  0
    [email protected]  1
    [email protected]  2
    [email protected]  3
    [email protected]  4
    [email protected]  5
    [email protected]  6

enter image description here

Improve this question

1 Answer 1

Reset to default
1

If I followed you correctly, you can use an aggregate query to count the number of orders per customer, and apply the arithmetic as needed:

SELECT c.email, (current_date - c.customer_reg_date) / 7 / COUNT(*) res
FROM customer_details c
INNER JOIN order_details o ON c.email = o.email
GROUP BY c.email, c.customer_reg_date

Demo on DB Fiddle:

| email        | res |
| ------------ | --- |
| [email protected] | 5   |
| [email protected] | 26  |
| [email protected] | 9   |

Sample Data:

CREATE TABLE order_details(
   email    VARCHAR(12) NOT NULL 
  ,order_id INTEGER  NOT NULL
);
INSERT INTO order_details(email,order_id) VALUES ('[email protected]',201);
INSERT INTO order_details(email,order_id) VALUES ('[email protected]',202);
INSERT INTO order_details(email,order_id) VALUES ('[email protected]',203);
INSERT INTO order_details(email,order_id) VALUES ('[email protected]',204);
INSERT INTO order_details(email,order_id) VALUES ('[email protected]',205);

CREATE TABLE customer_details(
   email             VARCHAR(12) NOT NULL PRIMARY KEY
  ,customer_reg_date DATE  NOT NULL
);
INSERT INTO customer_details(email,customer_reg_date) VALUES ('[email protected]','2019-05-24');
INSERT INTO customer_details(email,customer_reg_date) VALUES ('[email protected]','2019-07-15');
INSERT INTO customer_details(email,customer_reg_date) VALUES ('[email protected]','2019-03-29');
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.