how to find date difference from two different table in mys-CodePudding

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

CodePudding user response：

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