Spike: Data Loading Checklist for Socrata API Metrics

### Dependency
- [ ] Reopen when there is a need to analyze these results, or if the user flow is modified and it changes the amount of data we might expect to load

### Overview

We need to create a process for measuring Socrata API response times, given our use-cases, so that the design team knows how long their loading-screen content should remain on-screen.

### More Info (optional)

Design team is wanting to know whether to skip certain flows for very-fast response times, in the case that data always takes a certain time to load. I.e., we want to obtain a general minimum load time.

### Action Items

- [ ] Copy the template from the copy block in Resources into a comment

### Resources/Instructions

```mdx

### System Information
- Platform: (Postman, Thunder Client, etc)
- Request Status: (Open / Closed)
- Request Shortname (i.e. : Open Tickets From 2025 March, Electronic Waste)
- Request Duration (e.g. 10.57 seconds)

```

- Parent Ticket: https://github.com/hackforla/311-data/issues/1890
- Similar to: https://github.com/hackforla/311-data/issues/1716

Query Example: Open Tickets From 2025 March, Electronic Waste

```
https://data.lacity.org/resource/h73f-gn57.json?$where=status = 'Open' AND createddate between '2025-06-04' and '2025-06-18' AND requesttype = 'Electronic Waste'
```

Query Example: Open Tickets From 2025 Last Two Weeks, Bulky Items or Animal Remains

```
https://data.lacity.org/resource/h73f-gn57.json?$where=status = 'Open' AND createddate between '2025-06-04' and '2025-06-18' AND (requesttype = 'Bulky Items' OR requesttype = 'Animal Remains')
```

Query Example: Number of SRs Grouped By NC

```
https://data.lacity.org/resource/b7dx-7gc3.json?$select=ncname, nc as nc_id, status, COUNT(status) as total_open&$group=ncname, nc_id, status&$where=status = 'Open' AND createddate between '2024-10-26' and '2024-10-29T23:59:59'
```

Resource Base URLs

2025
```
https://data.lacity.org/resource/h73f-gn57.json
```

2024
```
https://data.lacity.org/resource/b7dx-7gc3.json
```

2023
```
https://data.lacity.org/resource/4a4x-mna2.json
```

All Service Request Types

```json
[
{
"requesttype": "Bulky Items",
"total": "167505"
},
{
"requesttype": "Dead Animal Removal",
"total": "6508"
},
{
"requesttype": "Electronic Waste",
"total": "9849"
},
{
"requesttype": "Feedback",
"total": "278"
},
{
"requesttype": "Graffiti Removal",
"total": "121791"
},
{
"requesttype": "Homeless Encampment",
"total": "24031"
},
{
"requesttype": "Illegal Dumping Pickup",
"total": "32735"
},
{
"requesttype": "Metal/Household Appliances",
"total": "26074"
},
{
"requesttype": "Multiple Streetlight Issue",
"total": "8149"
},
{
"requesttype": "Other",
"total": "2791"
},
{
"requesttype": "Report Water Waste",
"total": "357"
},
{
"requesttype": "Single Streetlight Issue",
"total": "6219"
}
]
```

How does our app __currently__ query data

This code block was abridged to focus on the specific fields we are querying, i.e. `CreatedDate` !
```jsx
async getAllRequests(startDate, endDate) {
const { conn } = this.context;
// ...
let selectSQL = '';

try {
if (startYear === endYear) {
// ...
const tableName = `requests_${startYear}`;
selectSQL = `SELECT * FROM ${tableName} WHERE CreatedDate BETWEEN '${startDate}' AND '${endDate}'`;
} else {
// If the dates span multiple years, create two queries and union them.
// ...
}

// ...
const requestsAsArrowTable = await conn.query(selectSQL);
const requests = ddbh.getTableData(requestsAsArrowTable);
return requests;
} catch (e) {
console.error("Error during database query execution:", e);
}
}
```

Notes on CreatedDate

There is one aspec-t that bothers me about how we are utilizing `StartDate` and `EndDate`: we are using these fields to filter the `CreatedDate` response field. But what question does this really answer? So, if I say, give me all of the Service Requests that were created-on June 1st through June15, that sounds useful for seeing what SRs were made in the last two weeks. But, what does it mean to ask "give me all of the SRs that were created-on Jan 1st through June 1st"?

Let's consider the two statuses, Open and Closed. Looking at all Open SRs created in the last two weeks is still useful -- it tells you "what's the latest activity (ongoing)". Finding all the Closed SRs created in the last two weeks gives you a slightly different lens: "what and where are all the quick-closing tickets?" Perhaps this can be used as a means to determine "which NCs close tickets very quickly", or "which SR types get closed very quickly". This also leads you to ask, "are these tickets closing very quickly because the responsible party is actually well resourced and is able to quickly address problems?". Conversely, you could ask, what NCs are closing tickets very quickly without actually solving the problem -- why are they allowed to do that? Are there any responsible parties that might want to close tickets quickly on their system, just to give the impression that they are actually being serviced?

Ramblings to continue...