Signup to First Dollar

Loopwire's sales team is trying to work out how long a new company takes to start paying. Creating an account and signing a contract are two separate events with two separate dates, and the gap between them is the figure the team calls time-to-convert. A company that has signed more than one contract over its life converted on the first of them, not the latest. Companies that have never signed anything have no gap to measure and stay out of the report. Some companies sign on the day they register and others take a few weeks, so the report has to carry a gap of zero as readily as one of a fortnight.

subscriptions — one row per contract. account_id points at a row in accounts. plan is the tier the customer bought. mrr is what that contract bills every month, in dollars. start_date is the day it began. A contract that has stopped carries the day it stopped in end_date; one that is still running has nothing there, shown as — below and stored as NULL.

accounts — one row per customer company. industry is the sector that company works in; signup_date is the day it created its Loopwire account.

Both tables already exist in the database — there is nothing to create or load.

Task: Write a query that returns two columns, company_name and days_to_convert, one row per company that has at least one contract, days_to_convert being the whole number of days from that company's signup date to the start date of its earliest contract. Shortest gap first; equal gaps are listed alphabetically by company name.

Example output

Shape only — these companies and gaps are invented:

(...3 more rows not shown)