Find dates common in different rows

A solution in your case is a recursive CTE.

A CTE (Common Table Expression) is a kind of temporary view that you define at the beginning of the query with WITH.
A recursive CTE is a CTE that calls itself, and is used to build data iteratively or recursively.

You need this because SQL doesn't have a native RANGE(start, end) type. You have to generate the rows one by one.

With a recursive CTE, you tell SQL:
Start at start_date. Then, each time, add a day. Repeat until you get past end_date.

Then you use this to generate the dates in between, which you can then count.

Example:

-- Create CTE
WITH DateExpanded AS (
    SELECT 
        task_name,
        start_date AS work_date,
        end_date
    FROM tasks
    UNION ALL
    SELECT 
        task_name,
        DATEADD(DAY, 1, work_date),
        end_date
    FROM DateExpanded
    WHERE work_date < end_date
)
-- Now group by data and count occurrences
SELECT 
    work_date,
    COUNT(*) AS occurrences
FROM DateExpanded
GROUP BY work_date
ORDER BY occurrences DESC
-- This piece is important if the date range is big, to avoid the default limit (100 layers) 
-- Be careful with that: set the maxrecursion only for the interval of time you need (maxdate - mindate)
-- If the recursion is too large or loops infinitely, the server will crash.
-- NEVER SET IT AT 0!!!
OPTION (MAXRECURSION 365);

SQL Server CTE Docs

With OP new desired result example: start and end date are min and max dates in table.
Query parts:

1. Generate date range with recursive CTE ("dates")
2. Join source table to date range.
3. Count active task's for date

with dates as(
  select min(start_date) as dt,max(end_date) as maxDt
  from sample
  union all
  select dateadd(day,1,dt) as dt,maxDt
  from dates
  where dt<maxDt
)
select dt,count(task_name) as qty
from dates d
left join sample t on d.dt between t.start_date and t.end_date
group by dt
order by count(task_name) desc,dt
option (maxrecursion 1000)
;

Option maxrecursion should be greater than difference between min and max date in source table in days.

Fiddle

old part of answer

if start_date and end_date is query parameters (or can be "hardcoded"), first, generate sequence of this date range.
Then join target table rows (table "sample" in example) with active tasks for this date
and count rows(active tasks for this date).

First query uses generate_series for this table range (available with SQL Server 2022).
In second example used recursive query to generate date range (works in earlier versions).

declare @start_date date ='2025-01-31';
declare @end_date date ='2025-02-04';
with dates as(
  select dateadd(day,value,@start_date) as dt
  from generate_series(0,datediff(day,@start_date,@end_date))
)
select dt,count(task_name) as qty
from dates d
left join sample t on d.dt between t.start_date and t.end_date
group by dt
order by dt -- or order by count(task_name)
;

We use count(task_name) instead count(*) to get count=0 for dates without active tasks.

Example with recursive geared date range:

declare @start_date date ='2025-01-31';
declare @end_date date ='2025-02-04';
with dates as(
  select @start_date as dt
  union all
  select dateadd(day,1,dt) 
  from dates
  where dt<@end_date
)
select dt,count(task_name) as qty
from dates d
left join sample t on d.dt between t.start_date and t.end_date
group by dt
order by dt
;

Column names "date" and "count" are not very suitable because they match reserved words and require quotation. Used "dt" and "qty" instead.

fiddle

Work with dates ranges, not individual days

The most efficient way will be to first work with dates ranges (not immediately denormalizing them to day-by-day rows):

with
  -- Determine every date where we have a count change: that is, every start_date or end_date:
  cuts as
  (
    select start_date d from t
    union -- Not union all, so that identical dates get merged: we want one occurrence of each.
    select dateadd(day, 1, end_date) from t -- The end date's cut happen on the next day at 00:00
  ),
  -- Now transform each task into as many entries as it crosses cuts;
  -- then group by cut date, and count how many tasks it crossed.
  crossings as
  (
    select c.d, count(t.task_name) count -- count(task_name) and not count(*): count only where we joined to our tasks table (to have the last, closing date, not being counted).
    from cuts c left join t on c.d between t.start_date and t.end_date
    group by c.d
  ),
  -- A dates range will be between a cut and the next one (lead()), as we ensured that cuts covered every date appearing in our tasks array.
  slices as
  (
    select
      d start_date,
      dateadd(day, -1, lead(d) over (order by d)) end_date, -- Get back to "last day of the dates range" instead of "first day after the range ended".
      count
    from crossings
  )
select * from slices order by count desc, start_date;

which returns:

(as seen in the first block of this fiddle)

… Then optionally get your daily results table

Now that we have a clean view over disjoint dates ranges with a stable count of running tasks,
if we want a more verbose day-by-day view we just have to "denormalize" each range to its individual days.

Here we can use one of two general techniques:

Joining with a serie of numbers

Each dates range is (end_date - start_date + 1)-long.
If we have a source of subsequent numbers from 0 to this length, we can join to it and emit a row whose date will be dateadd(day, thisnumber, start_date).

We have various possibilities for this source of numbers, but there are already a lot of stuff about it elsewhere.
Let's just choose one of these to demonstrate:

with
  -- Everything of our first part, but we replace the final select * from slices by what follows:
  -- Recurse over days of each range.
  alldays as
  (
    select dateadd(day, v.number, start_date) date, count
    from slices
    join master..spt_values v                           -- The spt_values hack gives us ranges until 2000 days
    on v.type = 'P'                                     -- Part of the spt_values hack.
    and v.number <= datediff(day, start_date, end_date) -- No more than the targeted range!
    where end_date is not null -- The null end_date represents "everything after our last end_date". We don't want to recurse infinitely, so filter it out.
  )
select date, count from alldays order by count desc, date;

Recursing over the dates ranges

SQL recursive Common Table Expressions is another way of iterating (more than recursing) over our dates ranges.
Note that we could use a 1-by-1 recursion (from 2024-12-01 to 2025-02-10), but it will be much more efficient if we make as many recursions run in parallel as they are unique dates ranges (instead of a 70 iterations process, we'll have 5 recursions of which the longest (12-01 to 01-14) will run for no more than 45 iterations).

with
  -- Everything of our first part, but we replace the final select * from slices by what follows:
  -- Recurse over days of each range.
  alldays as
  (
    select start_date date, count, end_date from slices where end_date is not null -- The null end_date represents "everything after our last end_date". We don't want to recurse infinitely, so remove it from the ranges to walk through.
    union all
    select dateadd(day, 1, date), count, end_date from alldays -- As we have computed our disjoint time spans, we know that the count is stable…
    where date < end_date -- … until we reach the end of the span.
  )
select date, count from alldays order by count desc, date
option (maxrecursion 100) -- If your tasks overlaps can span over more than 100 days, increase this value. 
;

Demo time!

Both solutions run in the same fiddle as above, returning your intended result: