how to use nested functions for different tables in postgresql?

Question

    **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

GMB · Accepted Answer · 2019-09-29 20:09:58Z

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');

Collectives™ on Stack Overflow

how to use nested functions for different tables in postgresql?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related