81 lines
2.8 KiB
Python
81 lines
2.8 KiB
Python
|
|
||
|
|
def mock_get_analytics(
|
||
|
|
metric: str = "volume",
|
||
|
|
group_by: str = "day",
|
||
|
|
sub_group_by: str = None,
|
||
|
|
date_from: str = None,
|
||
|
|
date_to: str = None,
|
||
|
|
isins: str = None,
|
||
|
|
continents: str = None
|
||
|
|
):
|
||
|
|
# Determine if we need to join metadata
|
||
|
|
needs_metadata = any([
|
||
|
|
group_by in ["name", "continent", "sector"],
|
||
|
|
sub_group_by in ["name", "continent", "sector"],
|
||
|
|
continents is not None
|
||
|
|
])
|
||
|
|
|
||
|
|
# Use prefixes only if joining
|
||
|
|
t_prefix = "t." if needs_metadata else ""
|
||
|
|
m_prefix = "m." if needs_metadata else ""
|
||
|
|
|
||
|
|
metrics_map = {
|
||
|
|
"volume": f"sum({t_prefix}price * {t_prefix}quantity)",
|
||
|
|
"count": f"count(*)",
|
||
|
|
"avg_price": f"avg({t_prefix}price)"
|
||
|
|
}
|
||
|
|
|
||
|
|
groups_map = {
|
||
|
|
"day": f"date_trunc('day', {t_prefix}timestamp)",
|
||
|
|
"month": f"date_trunc('month', {t_prefix}timestamp)",
|
||
|
|
"exchange": f"{t_prefix}exchange",
|
||
|
|
"isin": f"{t_prefix}isin",
|
||
|
|
"name": f"coalesce({m_prefix}name, {t_prefix}isin)" if needs_metadata else "isin",
|
||
|
|
"continent": f"coalesce({m_prefix}continent, 'Unknown')" if needs_metadata else "'Unknown'",
|
||
|
|
"sector": f"coalesce({m_prefix}sector, 'Unknown')" if needs_metadata else "'Unknown'"
|
||
|
|
}
|
||
|
|
|
||
|
|
selected_metric = metrics_map.get(metric, metrics_map["volume"])
|
||
|
|
selected_group = groups_map.get(group_by, groups_map["day"])
|
||
|
|
|
||
|
|
query = f"select {selected_group} as label"
|
||
|
|
|
||
|
|
if sub_group_by and sub_group_by in groups_map:
|
||
|
|
query += f", {groups_map[sub_group_by]} as sub_label"
|
||
|
|
|
||
|
|
query += f", {selected_metric} as value from trades"
|
||
|
|
if needs_metadata:
|
||
|
|
query += " t left join metadata m on t.isin = m.isin"
|
||
|
|
|
||
|
|
query += " where 1=1"
|
||
|
|
|
||
|
|
if date_from:
|
||
|
|
query += f" and {t_prefix}timestamp >= '{date_from}'"
|
||
|
|
if date_to:
|
||
|
|
query += f" and {t_prefix}timestamp <= '{date_to}'"
|
||
|
|
|
||
|
|
if isins:
|
||
|
|
isins_list = ",".join([f"'{i.strip()}'" for i in isins.split(",")])
|
||
|
|
query += f" and {t_prefix}isin in ({isins_list})"
|
||
|
|
|
||
|
|
if continents and needs_metadata:
|
||
|
|
cont_list = ",".join([f"'{c.strip()}'" for c in continents.split(",")])
|
||
|
|
query += f" and {m_prefix}continent in ({cont_list})"
|
||
|
|
|
||
|
|
query += " group by label"
|
||
|
|
if sub_group_by and sub_group_by in groups_map:
|
||
|
|
query += ", sub_label"
|
||
|
|
|
||
|
|
query += " order by label asc"
|
||
|
|
return query
|
||
|
|
|
||
|
|
# Test cases
|
||
|
|
print("Case 1: Basic analytics (reported error case)")
|
||
|
|
print(mock_get_analytics(metric="volume", group_by="day", date_from="2025-12-26", date_to="2026-01-25", isins="DE000LS1LUS9"))
|
||
|
|
|
||
|
|
print("\nCase 2: Filtering by continent (should join)")
|
||
|
|
print(mock_get_analytics(metric="volume", group_by="day", continents="Europe"))
|
||
|
|
|
||
|
|
print("\nCase 3: Grouping by name (should join)")
|
||
|
|
print(mock_get_analytics(metric="count", group_by="name"))
|