I'm collecting some basic statistics on our codebase and am trying to generate a query using the following schema data 

1. A files table holding all the files (synthetic Primary Key ID, unique path, and a region column which holds who the file belongs to.
2. A file\_stats table holding data for the files on a specific date (Primary Key is combination of date and file\_id)

---

```
CREATE TABLE files (
id INT PRIMARY KEY,
path VARCHAR(255) NOT NULL UNIQUE,
region VARCHAR(4) CHECK (region IN ('NYK', 'LDN', 'CORE', 'TKY')),
)

CREATE TABLE file_stats (
date DATE NOT NULL,
file_id INT NOT NULL REFERENCES files,
num_lines INT NOT NULL,

CONSTRAINT file_stats__pk PRIMARY KEY(date, file_id)
)

```

---

I'm trying to create a query which will return all combinations of dates and regions in the tables and the number of files for that combination.

The simple approach of 

```
SELECT date, region, COUNT(*) FROM file_stats fs, files f WHERE fs.file_id = f.id
GROUP BY date, region

```

doesn't work as not all regions are represnted at all dates.
I've tried 

```
SELECT 
d.date, 
r.region,
(SELECT COUNT(*) FROM file_stats fs, files f 
WHERE fs.file_id = file.id AND fs.date = d.date AND d.region = r.region
) AS num_files
FROM
(SELECT DISTINCT date FROM file_stats) AS d,
(SELECT DiSTINCT region FROM files) AS r

```

but the performance is unacceptable because of the nested subquery.

I've tried LEFT OUTER JOINS, but never seem to be able to make them work.
The database is SQLITE

Can anyone suggest a better query?