-2

I have two tables

1)LEAD TABLE (which have 3 columns)

Lead_ID || Created_Date || Industry

2)ACCOUNTS TABLE (which have 4 columns)

Account_ID||Created_Date|| Revenue_Range|| Lead_ID

How would I get the average number of days between a lead created and an account created

neha jha
  • 3
  • 2

1 Answers1

1

Don't pay attention to mess in data, I just randomly populated it. Query returns leadId and difference in days between lead.created_date and account.created_date.

Query:

create table Leads
(
    leadId int not null,
    created_date datetime,
    industry varchar(10),
    PRIMARY KEY (leadId)
);

create table Accounts
(
    accountId int not null,
    created_date datetime,
    revenue_range varchar(10),
    leadId int not null,
    FOREIGN KEY (leadId) REFERENCES Leads(leadId)
);

insert into Leads
values 
(1, '2020-01-01', 'a'),
(2, '2020-01-02', 'b'),
(3, '2020-01-03', 'c'),
(4, '2020-02-01', 'd'),
(5, '2020-03-01', 'e');

insert into Accounts
values 
(1, '2020-01-03', '1k', 1),
(2, '2020-03-10', '2k', 5),
(3, '2020-02-03', '3k', 2);

select 
 -- l.leadId,
 -- l.created_date as LeadCreatedDate,
 -- a.created_date as AccountCreatedDate,
 -- ABS is used because it returns with minus sign
 AVG(ABS(DATEDIFF(l.created_date, a.created_date))) as AvgDifferenceInDaysBetweenCreation
from Leads as l
inner join Accounts as a
on l.leadId = a.leadId;

You can try it out at SQLize Online

Stanislav Balia
  • 385
  • 1
  • 12
  • It worked Thankyou!! – neha jha Jan 06 '22 at 05:35
  • but we have to find average number in date difference – neha jha Jan 06 '22 at 05:41
  • @nehajha, edited my answer. Now it shows averages number in days between lead created date and account created date for all leads who have the row in account table. It can't show per each lead, as only it is relationship 1 to 1, meaning 1 lead row corresponds to 1 account row. If it is unexpected result, please elaborate more on your expected result and what you actually trying to achieve. – Stanislav Balia Jan 06 '22 at 07:49