[{"id":1,"slug":"list-tables","category":"basics","title_en":"List every table and view","title_he":"רשימת כל הטבלאות והתצוגות","question":"Which tables and views exist in the database?","sql":"SELECT name, type\nFROM sqlite_master\nWHERE type IN ('table', 'view')\n  AND name NOT LIKE 'sqlite_%'\n  AND name NOT GLOB '*_fts_*'\n  AND name NOT GLOB '*_rtree_*'\nORDER BY name","explanation":"sqlite_master is the catalogue of every schema object and can be read with a plain SELECT. The GLOB filters hide the internal shadow tables that FTS5 and R*Tree create behind settlements_fts, streets_fts, neighborhoods_fts and parcels_rtree.","tables":["sqlite_master"],"verified":{"rows":49,"ms":0.1,"at":"2026-09-28"}},{"id":2,"slug":"show-create-table","category":"basics","title_en":"Show the CREATE statement of a table","title_he":"הצגת הגדרת טבלה (CREATE)","question":"What is the exact DDL of the deals and settlements tables?","sql":"SELECT name, sql\nFROM sqlite_master\nWHERE type = 'table' AND name IN ('deals', 'settlements')","explanation":"The sql column keeps the original CREATE TABLE text, including inline comments that document the deals columns. Use it to see declared types, NOT NULL constraints and primary keys.","tables":["sqlite_master"],"verified":{"rows":2,"ms":0,"at":"2026-09-28"}},{"id":3,"slug":"table-columns","category":"basics","title_en":"List the columns of a table","title_he":"רשימת העמודות של טבלה","question":"What columns does the neighborhoods table have, with their types?","sql":"SELECT cid, name, type, \"notnull\" AS not_null, pk\nFROM pragma_table_info('neighborhoods')\nORDER BY cid","explanation":"pragma_table_info() is a read-only table-valued function, so it works inside a SELECT. It returns one row per column with its declared type, NOT NULL flag and primary-key position.","tables":["neighborhoods"],"verified":{"rows":47,"ms":0.1,"at":"2026-09-28"}},{"id":4,"slug":"table-indexes","category":"basics","title_en":"List the indexes of the deals table","title_he":"רשימת האינדקסים של טבלת העסקאות","question":"Which indexes exist on deals, so I can write queries that use them?","sql":"SELECT name, sql\nFROM sqlite_master\nWHERE type = 'index' AND tbl_name = 'deals' AND sql IS NOT NULL\nORDER BY name","explanation":"Filter deals by settlement_code, (gush, chelka), property_group, deal_date, deal_amount or price_per_sqm so SQLite can use one of these indexes. A filter on area, year_built or in_stats alone forces a scan of 3.18M rows.","tables":["sqlite_master"],"verified":{"rows":11,"ms":0,"at":"2026-09-28"}},{"id":5,"slug":"meta-time-windows","category":"basics","title_en":"Stats anchor date and time windows","title_he":"תאריך העוגן וחלונות הזמן","question":"What date do the statistics run to, and what are the 12-month, 24-month and 5-year windows?","sql":"SELECT key, value\nFROM meta\nWHERE key LIKE 'window_%'\n   OR key IN ('schema_version', 'stats_anchor_date', 'incomplete_from_month', 'min_date', 'max_date', 'built_at')\nORDER BY key","explanation":"Every precomputed window ends at stats_anchor_date (2026-06-30), not at max_date: later months are still incomplete because of reporting lag. Use these bounds for your own date filters so your numbers match the precomputed columns.","tables":["meta"],"verified":{"rows":16,"ms":0,"at":"2026-09-28"}},{"id":6,"slug":"property-groups","category":"basics","title_en":"Property groups and their deal counts","title_he":"סוגי נכסים ומספר העסקאות","question":"What property types are there, which are residential, and how many deals does each have?","sql":"SELECT key, label_he, label_plural_he, is_residential, deals\nFROM property_groups\nORDER BY sort","explanation":"property_group is the normalised deal type used everywhere (deals, agg_* tables). The agg tables also have two pseudo-groups: all_residential (the 4 residential groups) and all (every deal, counts and volume only).","tables":["property_groups"],"verified":{"rows":9,"ms":0,"at":"2026-09-28"}},{"id":7,"slug":"row-counts","category":"basics","title_en":"Row counts of the main tables","title_he":"מספר השורות בטבלאות העיקריות","question":"How many rows does each main table have?","sql":"SELECT 'deals' AS table_name, CAST(value AS INTEGER) AS row_count FROM meta WHERE key = 'total_deals'\nUNION ALL\nSELECT 'settlements', CAST(value AS INTEGER) FROM meta WHERE key = 'settlements'\nUNION ALL\nSELECT 'gushim', CAST(value AS INTEGER) FROM meta WHERE key = 'gushim'\nUNION ALL\nSELECT 'parcels', CAST(value AS INTEGER) FROM meta WHERE key = 'parcels'\nUNION ALL\nSELECT j.key, j.value FROM json_each((SELECT value FROM meta WHERE key = 'enrich_counts')) AS j","explanation":"meta stores counts at build time, which is instant, whereas count(*) over deals walks an index of 3.18M entries. enrich_counts is a JSON object, and json_each() turns it into rows.","tables":["meta"],"verified":{"rows":34,"ms":0,"at":"2026-09-28"}},{"id":8,"slug":"data-sources","category":"basics","title_en":"Data sources and licences","title_he":"מקורות המידע והרישיונות","question":"Where does the enrichment data come from and under what licence?","sql":"SELECT json_extract(j.value, '$.bundle') AS bundle,\n       json_extract(j.value, '$.name') AS source,\n       json_extract(j.value, '$.licence') AS licence,\n       json_extract(j.value, '$.url') AS url\nFROM json_each((SELECT value FROM meta WHERE key = 'sources')) AS j\nORDER BY bundle, source","explanation":"meta.sources is a JSON array with one object per public source. json_each() plus json_extract() turn it into a table. Cite these sources when you publish derived numbers.","tables":["meta"],"verified":{"rows":30,"ms":0.2,"at":"2026-09-28"}},{"id":9,"slug":"settlement-profile","category":"settlements","title_en":"Settlement headline numbers","title_he":"נתוני מפתח של יישוב","question":"What are the key price statistics for Haifa over the last 12 months?","sql":"SELECT code, name, name_en, district, population,\n       deals_12m, n_stats_12m, median_price_12m,\n       n_ppsqm_12m, median_ppsqm_12m,\n       median_ppsqm_existing_12m, median_ppsqm_new_12m,\n       ppsqm_change_pct, rank_ppsqm, n_discount_12m, deals_after_anchor\nFROM settlements\nWHERE name = 'חיפה'","explanation":"The settlements table holds precomputed 12-month statistics (2025-07-01 to 2026-06-30) over all residential groups under the in_stats rule. ppsqm_change_pct compares existing-stock prices only and is NULL unless both windows have at least 50 deals.","tables":["settlements"],"verified":{"rows":1,"ms":0.1,"at":"2026-09-28"}},{"id":10,"slug":"most-expensive-settlements","category":"settlements","title_en":"Most expensive settlements per m²","title_he":"היישובים היקרים ביותר למ״ר","question":"Which cities have the highest price per square metre?","sql":"SELECT rank_ppsqm, name, name_en, median_ppsqm_12m, n_ppsqm_12m, median_price_12m, ppsqm_change_pct\nFROM settlements\nWHERE rank_ppsqm IS NOT NULL\nORDER BY rank_ppsqm\nLIMIT 20","explanation":"rank_ppsqm is only set for settlements with at least 30 deals with a price per m² in 12 months, spread over at least 5 parcels. That keeps one luxury project from topping the list. The partial index ix_settlements_rank serves this query.","tables":["settlements"],"verified":{"rows":20,"ms":0,"at":"2026-09-28"}},{"id":11,"slug":"price-change-leaders","category":"settlements","title_en":"Biggest price risers and fallers","title_he":"העליות והירידות הגדולות במחיר","question":"Which cities saw the largest change in price per m² over the last year?","sql":"SELECT name, ppsqm_change_pct,\n       median_ppsqm_existing_prev12m, median_ppsqm_existing_12m,\n       n_ppsqm_existing_12m, ppsqm_change_method\nFROM settlements\nWHERE ppsqm_change_pct IS NOT NULL\nORDER BY ppsqm_change_pct DESC\nLIMIT 20","explanation":"The change compares the existing-stock median (is_new_build = 0) of the 12 months to the anchor with the 12 months before. It is not a pooled median ratio, which would swing with the share of new projects. Sort ASC to get the fallers.","tables":["settlements"],"verified":{"rows":20,"ms":0.2,"at":"2026-09-28"}},{"id":12,"slug":"districts-overview","category":"settlements","title_en":"District overview","title_he":"סקירת מחוזות","question":"How do the seven districts compare on price and activity?","sql":"SELECT name, n_settlements, deals_total, deals_12m,\n       median_price_12m, n_ppsqm_12m, median_ppsqm_12m, ppsqm_change_pct\nFROM districts\nORDER BY median_ppsqm_12m DESC","explanation":"districts has one row per CBS district with the same 12-month rules as settlements. יהודה והשומרון has very few deals because the Tax Authority register barely covers the West Bank.","tables":["districts"],"verified":{"rows":7,"ms":0,"at":"2026-09-28"}},{"id":13,"slug":"busiest-settlements-12m","category":"settlements","title_en":"Busiest settlements in the last 12 months","title_he":"היישובים הפעילים ביותר ב-12 החודשים האחרונים","question":"Where were the most real-estate deals in the last 12 months?","sql":"SELECT name, deals_12m, deals_residential_12m, deals_prev12m, deals_after_anchor, total_volume_12m\nFROM settlements\nORDER BY deals_12m DESC\nLIMIT 20","explanation":"deals_12m counts every deal in the window, including partial, outlier and multi-unit rows (transaction volume). deals_after_anchor are deals reported after 2026-06-30 so far; do not read deals_12m vs deals_prev12m as a trend, because late reports keep arriving.","tables":["settlements"],"verified":{"rows":20,"ms":0.2,"at":"2026-09-28"}},{"id":14,"slug":"district-cities-ranked","category":"settlements","title_en":"Cities of a district ranked by price","title_he":"דירוג ערי מחוז לפי מחיר","question":"Rank the settlements of the Central district by median price per m².","sql":"SELECT name, median_ppsqm_12m, n_ppsqm_12m, median_price_12m, median_price_4rooms_12m, ppsqm_change_pct\nFROM settlements\nWHERE district = 'המרכז' AND n_ppsqm_12m >= 30\nORDER BY median_ppsqm_12m DESC","explanation":"district uses the CBS spelling (המרכז, תל אביב, ירושלים, חיפה, הצפון, הדרום, יהודה והשומרון) and is indexed. The n_ppsqm_12m >= 30 gate hides medians based on a handful of deals.","tables":["settlements"],"verified":{"rows":29,"ms":0.1,"at":"2026-09-28"}},{"id":15,"slug":"new-build-premium","category":"settlements","title_en":"New-build premium by city","title_he":"הפרמיה על דירות חדשות לפי עיר","question":"How much more do new apartments cost per m² than existing ones, by city?","sql":"SELECT name, median_ppsqm_new_12m, n_ppsqm_new_12m,\n       median_ppsqm_existing_12m, n_ppsqm_existing_12m,\n       round(100.0 * median_ppsqm_new_12m / median_ppsqm_existing_12m - 100, 1) AS new_build_premium_pct\nFROM settlements\nWHERE n_ppsqm_new_12m >= 50 AND n_ppsqm_existing_12m >= 50\nORDER BY new_build_premium_pct DESC","explanation":"New builds are deals with year_built >= deal year - 1, including off-plan sales. Discount-lottery units are already excluded from both medians, so a premium here is the market new-build premium.","tables":["settlements"],"verified":{"rows":59,"ms":0.2,"at":"2026-09-28"}},{"id":16,"slug":"national-yearly-apartment-prices","category":"trends","title_en":"National apartment prices by year","title_he":"מחירי דירות ארציים לפי שנה","question":"How has the national median apartment price changed since 1998?","sql":"SELECT year, deals, n_stats, median_price, n_ppsqm, median_ppsqm, is_incomplete\nFROM agg_national_year\nWHERE property_group = 'apartment' AND rooms_bucket = 'all'\nORDER BY year","explanation":"agg_national_year holds precomputed true medians under the in_stats rule, so there is no need to scan deals. The 2026 row has is_incomplete = 1 (reporting lag): drop it or label it partial.","tables":["agg_national_year"],"verified":{"rows":29,"ms":0,"at":"2026-09-28"}},{"id":17,"slug":"city-quarterly-series","category":"trends","title_en":"Quarterly price series of a city","title_he":"סדרת מחירים רבעונית של עיר","question":"Show the quarterly median price per m² of apartments in Tel Aviv since 2015.","sql":"SELECT quarter, deals, n_ppsqm, median_ppsqm, median_price, is_incomplete\nFROM agg_settlement_quarter\nWHERE settlement_code = 5000 AND property_group = 'apartment' AND rooms_bucket = 'all'\n  AND quarter >= '2015-Q1'\nORDER BY quarter","explanation":"The primary key (settlement_code, property_group, rooms_bucket, quarter) makes this a direct range lookup. Quarter strings sort correctly. Quarters with no deals are absent, and 2026-Q3 has is_incomplete = 1.","tables":["agg_settlement_quarter"],"verified":{"rows":47,"ms":0,"at":"2026-09-28"}},{"id":18,"slug":"compare-cities-pivot","category":"trends","title_en":"Compare cities side by side","title_he":"השוואת ערים זו לצד זו","question":"Compare the yearly apartment price per m² of Tel Aviv, Jerusalem and Haifa since 2010.","sql":"SELECT year,\n       max(CASE WHEN settlement_code = 5000 THEN median_ppsqm END) AS tel_aviv,\n       max(CASE WHEN settlement_code = 3000 THEN median_ppsqm END) AS jerusalem,\n       max(CASE WHEN settlement_code = 4000 THEN median_ppsqm END) AS haifa,\n       max(is_incomplete) AS is_incomplete\nFROM agg_settlement_year\nWHERE settlement_code IN (5000, 3000, 4000) AND property_group = 'apartment' AND year >= 2010\nGROUP BY year\nORDER BY year","explanation":"Conditional aggregation (max(CASE ...)) pivots one row per city-year into one column per city. Look up codes in settlements (5000 Tel Aviv, 3000 Jerusalem, 4000 Haifa).","tables":["agg_settlement_year"],"verified":{"rows":17,"ms":0,"at":"2026-09-28"}},{"id":19,"slug":"national-monthly-volume","category":"trends","title_en":"National monthly deal volume","title_he":"היקף עסקאות ארצי חודשי","question":"How many deals and how much money changed hands each month over the last three years?","sql":"SELECT month, deals, round(total_volume / 1e9, 2) AS volume_billion_ils, is_incomplete\nFROM agg_national_month\nWHERE property_group = 'all' AND month >= '2023-07'\nORDER BY month","explanation":"Use property_group = 'all' for counts and money volume (its median columns are NULL). total_volume only sums credible amounts. The months after 2026-06 are incomplete, so their drop is reporting lag, not a crash.","tables":["agg_national_month"],"verified":{"rows":39,"ms":0,"at":"2026-09-28"}},{"id":20,"slug":"price-by-rooms-2025","category":"trends","title_en":"Apartment prices by number of rooms","title_he":"מחירי דירות לפי מספר חדרים","question":"What did apartments cost nationally in 2025 by number of rooms?","sql":"SELECT rooms_bucket, deals, n_stats, median_price, median_ppsqm, median_area\nFROM agg_national_year\nWHERE property_group = 'apartment' AND year = 2025 AND rooms_bucket <> 'all'\nORDER BY rooms_bucket","explanation":"rooms_bucket is 1-2 (< 3 rooms), 3 (3-3.5), 4 (4-4.5), 5 (5-5.5) and 6+. The 'all' bucket also includes rows with unknown rooms, so the buckets do not add up to it.","tables":["agg_national_year"],"verified":{"rows":5,"ms":0,"at":"2026-09-28"}},{"id":21,"slug":"district-quarterly-pivot","category":"trends","title_en":"District price trend by quarter","title_he":"מגמת מחירים רבעונית לפי מחוז","question":"How has the residential price per m² moved in each district quarter by quarter since 2023?","sql":"SELECT quarter,\n       max(CASE WHEN district = 'תל אביב' THEN median_ppsqm END) AS tel_aviv,\n       max(CASE WHEN district = 'ירושלים' THEN median_ppsqm END) AS jerusalem,\n       max(CASE WHEN district = 'המרכז' THEN median_ppsqm END) AS center,\n       max(CASE WHEN district = 'חיפה' THEN median_ppsqm END) AS haifa,\n       max(CASE WHEN district = 'הצפון' THEN median_ppsqm END) AS north,\n       max(CASE WHEN district = 'הדרום' THEN median_ppsqm END) AS south,\n       max(is_incomplete) AS is_incomplete\nFROM agg_district_quarter\nWHERE quarter >= '2023-Q1'\nGROUP BY quarter\nORDER BY quarter","explanation":"agg_district_quarter is residential only (all_residential) and has no property_group column. For other groups use agg_district_year.","tables":["agg_district_quarter"],"verified":{"rows":15,"ms":0.1,"at":"2026-09-28"}},{"id":22,"slug":"seasonality-by-month","category":"trends","title_en":"Seasonality of deals by calendar month","title_he":"עונתיות העסקאות לפי חודש","question":"Which months of the year have the most real-estate deals?","sql":"SELECT substr(month, 6, 2) AS calendar_month,\n       round(avg(deals)) AS avg_deals,\n       min(deals) AS min_deals,\n       max(deals) AS max_deals\nFROM agg_national_month\nWHERE property_group = 'all_residential' AND month BETWEEN '2010-01' AND '2025-12'\nGROUP BY calendar_month\nORDER BY calendar_month","explanation":"Averaging deal counts is fine; averaging medians is not. The range stops at complete years, before the incomplete months after the anchor. December usually tops the list because of year-end tax planning.","tables":["agg_national_month"],"verified":{"rows":12,"ms":0.1,"at":"2026-09-28"}},{"id":23,"slug":"ten-year-risers","category":"trends","title_en":"Biggest 10-year price risers","title_he":"העליות הגדולות בעשור","question":"Which cities had the biggest rise in apartment price per m² from 2015 to 2025?","sql":"SELECT s.name, a.median_ppsqm AS ppsqm_2015, b.median_ppsqm AS ppsqm_2025,\n       round(1.0 * b.median_ppsqm / a.median_ppsqm, 2) AS multiple,\n       a.n_ppsqm AS n_2015, b.n_ppsqm AS n_2025\nFROM agg_settlement_year a\nJOIN agg_settlement_year b\n  ON b.settlement_code = a.settlement_code AND b.property_group = a.property_group AND b.year = 2025\nJOIN settlements s ON s.code = a.settlement_code\nWHERE a.property_group = 'apartment' AND a.year = 2015\n  AND a.n_ppsqm >= 100 AND b.n_ppsqm >= 100\nORDER BY multiple DESC\nLIMIT 20","explanation":"A self-join of agg_settlement_year pairs each city's 2015 and 2025 rows. The n >= 100 gate in both years keeps small samples out. Part of a periphery rise can be mix: new neighbourhoods replacing old stock in the sales.","tables":["agg_settlement_year","settlements"],"verified":{"rows":20,"ms":0.7,"at":"2026-09-28"}},{"id":24,"slug":"latest-deals-national","category":"deals","title_en":"Latest deals nationwide","title_he":"העסקאות האחרונות בארץ","question":"What are the 25 most recent deals in the registry?","sql":"SELECT id, deal_date, settlement, property_group, rooms, area, deal_amount, price_per_sqm,\n       portion, is_outlier, is_multi_unit, is_discount_project\nFROM deals\nORDER BY deal_date DESC, id DESC\nLIMIT 25","explanation":"ORDER BY deal_date DESC, id DESC walks ix_deals_date backwards and stops after 25 rows. Show portion when it is below 1 (a share was sold), and flag outlier, multi-unit and discount-project rows rather than hiding them.","tables":["deals"],"verified":{"rows":25,"ms":0,"at":"2026-09-28"}},{"id":25,"slug":"latest-deals-city","category":"deals","title_en":"Latest deals in a city","title_he":"העסקאות האחרונות בעיר","question":"Show the 50 newest deals in Tel Aviv.","sql":"SELECT id, deal_date, property_group, rooms, area, deal_amount, price_per_sqm,\n       gush, chelka, portion, is_outlier, is_multi_unit, is_discount_project\nFROM deals\nWHERE settlement_code = 5000\nORDER BY deal_date DESC, id DESC\nLIMIT 50","explanation":"ix_deals_settlement_date (settlement_code, deal_date, id, ...) returns the rows already in order, so the query stops after 50 rows. Always filter by settlement_code (an integer) rather than by the settlement name.","tables":["deals"],"verified":{"rows":50,"ms":0.1,"at":"2026-09-28"}},{"id":26,"slug":"filtered-deal-search","category":"deals","title_en":"Filtered deal search","title_he":"חיפוש עסקאות עם מסננים","question":"Find apartments in Tel Aviv with 3 to 4.5 rooms that sold for 2-4 million ILS in the last 12 months.","sql":"SELECT id, deal_date, rooms, area, deal_amount, price_per_sqm, gush, chelka, year_built,\n       is_outlier, is_multi_unit\nFROM deals\nWHERE settlement_code = 5000\n  AND property_group = 'apartment'\n  AND deal_date BETWEEN '2025-07-01' AND '2026-06-30'\n  AND rooms BETWEEN 3 AND 4.5\n  AND deal_amount BETWEEN 2000000 AND 4000000\n  AND is_full_deal = 1\nORDER BY deal_date DESC, id DESC\nLIMIT 100","explanation":"The equality filters (settlement_code, property_group) and the date range match ix_deals_settlement_group_date. That index also carries rooms and deal_amount, so those filters run inside the index. is_full_deal = 1 makes the amount the whole unit's price rather than a share.","tables":["deals"],"verified":{"rows":100,"ms":0.2,"at":"2026-09-28"}},{"id":27,"slug":"keyset-pagination","category":"deals","title_en":"Keyset pagination of a deal list","title_he":"דפדוף בעסקאות לפי מפתח (keyset)","question":"How do I get the next page of Jerusalem deals after the last row I already have?","sql":"SELECT id, deal_date, property_group, rooms, deal_amount, price_per_sqm\nFROM deals\nWHERE settlement_code = 3000\n  AND (deal_date, id) < ('2026-05-31', 99999999)\nORDER BY deal_date DESC, id DESC\nLIMIT 50","explanation":"Pass the (deal_date, id) of the last row of the previous page as the cursor. The row-value comparison continues exactly where the page ended, and each page costs the same however deep you go. OFFSET costs O(offset).","tables":["deals"],"verified":{"rows":50,"ms":0,"at":"2026-09-28"}},{"id":28,"slug":"top-residential-deals-12m","category":"deals","title_en":"Most expensive homes of the last 12 months","title_he":"הדירות היקרות ביותר ב-12 החודשים האחרונים","question":"What were the most expensive home sales in Israel in the 12 months to June 2026?","sql":"SELECT id, deal_date, settlement, property_group, rooms, area, deal_amount, price_per_sqm, gush, chelka\nFROM deals\nWHERE deal_date BETWEEN '2025-07-01' AND '2026-06-30'\n  AND is_residential = 1\n  AND is_full_deal = 1\n  AND is_outlier = 0\n  AND is_multi_unit = 0\nORDER BY deal_amount DESC\nLIMIT 20","explanation":"Exclude outliers and multi-unit rows, or \"most expensive\" fills up with bulk and non-market rows. Keep full deals only, because a partial deal's amount is for a share. The date range keeps the sort to about 110k rows.","tables":["deals"],"verified":{"rows":20,"ms":96.1,"at":"2026-09-28"}},{"id":29,"slug":"top-ppsqm-city-year","category":"deals","title_en":"Highest price per m² in a city","title_he":"המחיר הגבוה ביותר למ״ר בעיר","question":"Which Jerusalem homes sold at the highest price per m² in 2025?","sql":"SELECT id, deal_date, property_group, rooms, area, deal_amount, price_per_sqm, gush, chelka\nFROM deals\nWHERE settlement_code = 3000\n  AND price_per_sqm IS NOT NULL\n  AND in_stats = 1\n  AND deal_date BETWEEN '2025-01-01' AND '2025-12-31'\nORDER BY price_per_sqm DESC\nLIMIT 20","explanation":"price_per_sqm is only set for residential full deals with a plausible area. in_stats = 1 drops outliers, multi-unit and discount-project rows. ix_deals_settlement_ppsqm walks the city's prices from the top down.","tables":["deals"],"verified":{"rows":20,"ms":3,"at":"2026-09-28"}},{"id":30,"slug":"cheapest-4-rooms-city","category":"deals","title_en":"Cheapest 4-room apartments in a city","title_he":"דירות 4 חדרים הזולות ביותר בעיר","question":"What were the cheapest 4-room apartments sold in Haifa in the last 12 months?","sql":"SELECT id, deal_date, rooms, area, deal_amount, price_per_sqm, gush, chelka, year_built\nFROM deals\nWHERE settlement_code = 4000\n  AND property_group = 'apartment'\n  AND deal_date BETWEEN '2025-07-01' AND '2026-06-30'\n  AND rooms >= 4 AND rooms < 5\n  AND in_stats = 1\nORDER BY deal_amount\nLIMIT 20","explanation":"in_stats = 1 removes the non-market rows (₪1 transfers, partial shares, bulk and discount-lottery sales) that would otherwise be the \"cheapest\". rooms >= 4 AND rooms < 5 is the same as rooms_bucket = '4'.","tables":["deals"],"verified":{"rows":20,"ms":1.2,"at":"2026-09-28"}},{"id":31,"slug":"deal-types-in-city","category":"deals","title_en":"Deal types in a city","title_he":"סוגי העסקאות בעיר","question":"What kinds of properties were sold in Haifa in the last 12 months?","sql":"SELECT property_group, count(*) AS deals,\n       sum(CASE WHEN is_full_deal = 1 THEN 0 ELSE 1 END) AS partial_or_unknown_share\nFROM deals\nWHERE settlement_code = 4000\n  AND deal_date BETWEEN '2025-07-01' AND '2026-06-30'\nGROUP BY property_group\nORDER BY deals DESC","explanation":"Counts include every deal (transaction volume). The settlement and date range match ix_deals_settlement_date. is_full_deal = 0 covers partial shares and unknown portions.","tables":["deals"],"verified":{"rows":8,"ms":3.7,"at":"2026-09-28"}},{"id":32,"slug":"new-builds-city","category":"deals","title_en":"New-build sales in a city","title_he":"מכירות דירות חדשות בעיר","question":"Which new-build apartments sold in Netanya most recently?","sql":"SELECT id, deal_date, rooms, area, deal_amount, price_per_sqm, year_built, gush, chelka,\n       is_discount_project, discount_source\nFROM deals\nWHERE settlement_code = 7400\n  AND property_group = 'apartment'\n  AND deal_date BETWEEN '2025-07-01' AND '2026-06-30'\n  AND is_new_build = 1\nORDER BY deal_date DESC, id DESC\nLIMIT 50","explanation":"is_new_build = 1 means year_built >= deal year - 1 (new or off-plan). is_new_build is not in the index, so keep the settlement and date range. Discount-lottery units are flagged in is_discount_project.","tables":["deals"],"verified":{"rows":50,"ms":0.1,"at":"2026-09-28"}},{"id":33,"slug":"parcel-deal-history","category":"parcels-geo","title_en":"Deal history of a parcel","title_he":"היסטוריית העסקאות בחלקה","question":"What deals were recorded at gush 7104, chelka 289 (a big Tel Aviv building)?","sql":"SELECT id, deal_date, sub_chelka, property_group, rooms, area, deal_amount, price_per_sqm,\n       portion, year_built, is_outlier, is_multi_unit\nFROM deals\nWHERE gush = 7104 AND chelka = 289\nORDER BY deal_date DESC, id DESC\nLIMIT 200","explanation":"The registry identifies property by gush (block), chelka (parcel) and sub_chelka (unit), not by address. ix_deals_gush_chelka (gush, chelka, deal_date) serves this lookup.","tables":["deals"],"verified":{"rows":200,"ms":0.3,"at":"2026-09-28"}},{"id":34,"slug":"parcel-summary","category":"parcels-geo","title_en":"Parcel summary with address","title_he":"תקציר חלקה כולל כתובת","question":"Summarise parcel 7104/289: location, activity, prices and street address.","sql":"SELECT p.gush, p.chelka, p.settlement_code, p.lat, p.lon, p.geo_precision, p.geo_source, p.uncertainty_m,\n       p.deals_total, p.deals_5y, p.n_units, p.first_deal_date, p.last_deal_date, p.last_price,\n       p.n_ppsqm_5y, p.median_ppsqm_5y, p.dominant_group,\n       a.address_label, a.street, a.house_numbers, a.street_source,\n       b.n_buildings, b.max_levels\nFROM parcels p\nLEFT JOIN parcel_address a ON a.gush = p.gush AND a.chelka = p.chelka\nLEFT JOIN parcel_buildings b ON b.gush = p.gush AND b.chelka = p.chelka\nWHERE p.gush = 7104 AND p.chelka = 289","explanation":"(gush, chelka) is the natural key of parcels and of every parcel-level enrichment table. address_label is NULL when OpenStreetMap only gives a nearby street (street_source = 'osm_nearest_street'): say \"near street X\", never present it as the address.","tables":["parcels","parcel_address","parcel_buildings"],"verified":{"rows":1,"ms":0,"at":"2026-09-28"}},{"id":35,"slug":"gush-yearly-series","category":"parcels-geo","title_en":"Yearly price series of a gush","title_he":"סדרת מחירים שנתית של גוש","question":"How has the price per m² in Jerusalem gush 30435 evolved year by year?","sql":"SELECT year, deals, n_stats, median_price, n_ppsqm, median_ppsqm, is_incomplete\nFROM agg_gush_year\nWHERE gush = 30435\nORDER BY year","explanation":"agg_gush_year counts all groups in deals but computes the price columns over residential in_stats deals only. Gushim are small, so check n_ppsqm before quoting a year's median.","tables":["agg_gush_year"],"verified":{"rows":29,"ms":0,"at":"2026-09-28"}},{"id":36,"slug":"gushim-of-city","category":"parcels-geo","title_en":"Gushim of a city ranked by price","title_he":"גושי העיר מדורגים לפי מחיר","question":"Which blocks (gushim) in Haifa are the most expensive, and how did they change?","sql":"SELECT gush, deals_24m, n_ppsqm_24m, median_ppsqm_24m, median_ppsqm_prev24m, change_pct, n_discount_24m, lat, lon\nFROM gushim\nWHERE settlement_code = 4000 AND n_ppsqm_24m >= 20\nORDER BY median_ppsqm_24m DESC\nLIMIT 30","explanation":"Gush statistics use a 24-month window (2024-07-01 to 2026-06-30). change_pct compares existing stock with the previous 24 months and is only set when both windows have at least 20 such deals.","tables":["gushim"],"verified":{"rows":30,"ms":0.1,"at":"2026-09-28"}},{"id":37,"slug":"repeat-sales","category":"parcels-geo","title_en":"Repeat sales of the same unit","title_he":"מכירות חוזרות של אותה יחידה","question":"Which apartments in gush 6212 were sold more than once, and how much did their price grow per year?","sql":"WITH sales AS (\n  SELECT gush, chelka, sub_chelka, deal_date, deal_amount,\n         lag(deal_date) OVER w AS prev_date,\n         lag(deal_amount) OVER w AS prev_amount\n  FROM deals\n  WHERE gush = 6212 AND sub_chelka > 0 AND in_stats = 1\n  WINDOW w AS (PARTITION BY chelka, sub_chelka ORDER BY deal_date)\n)\nSELECT chelka, sub_chelka, prev_date, prev_amount, deal_date, deal_amount,\n       round((julianday(deal_date) - julianday(prev_date)) / 365.25, 1) AS years_held,\n       round(100 * (pow(1.0 * deal_amount / prev_amount, 365.25 / (julianday(deal_date) - julianday(prev_date))) - 1), 1) AS annual_growth_pct\nFROM sales\nWHERE prev_date IS NOT NULL AND julianday(deal_date) - julianday(prev_date) >= 365\nORDER BY deal_date DESC\nLIMIT 50","explanation":"A unit is (gush, chelka, sub_chelka) with sub_chelka > 0. lag() over a window partitioned by unit pairs each sale with the previous one. in_stats = 1 keeps only full, market-priced sales, and pow() gives the compound annual growth.","tables":["deals"],"verified":{"rows":50,"ms":6.2,"at":"2026-09-28"}},{"id":38,"slug":"units-last-sale","category":"parcels-geo","title_en":"Last sale of every unit in a building","title_he":"המכירה האחרונה של כל יחידה בבניין","question":"For each apartment unit in parcel 7104/289, what was its most recent sale?","sql":"SELECT sub_chelka, deal_date, rooms, area, deal_amount, price_per_sqm, portion, n_sales\nFROM (\n  SELECT sub_chelka, deal_date, rooms, area, deal_amount, price_per_sqm, portion,\n         row_number() OVER (PARTITION BY sub_chelka ORDER BY deal_date DESC, id DESC) AS rn,\n         count(*) OVER (PARTITION BY sub_chelka) AS n_sales\n  FROM deals\n  WHERE gush = 7104 AND chelka = 289 AND sub_chelka > 0 AND is_residential = 1\n)\nWHERE rn = 1\nORDER BY deal_date DESC\nLIMIT 100","explanation":"row_number() = 1 per partition is the standard \"latest row per group\" idiom. count(*) OVER the same partition adds how many times each unit was sold.","tables":["deals"],"verified":{"rows":100,"ms":3.8,"at":"2026-09-28"}},{"id":39,"slug":"parcel-vs-gush-vs-city","category":"parcels-geo","title_en":"Parcel vs its gush vs its city","title_he":"חלקה מול הגוש ומול העיר","question":"Is parcel 6213/1471 in Tel Aviv expensive compared with its block and the city?","sql":"SELECT p.gush, p.chelka,\n       p.median_ppsqm_5y AS parcel_ppsqm_5y, p.n_ppsqm_5y AS parcel_n,\n       g.median_ppsqm_5y AS gush_ppsqm_5y, g.n_ppsqm_5y AS gush_n,\n       s.median_ppsqm_5y AS city_ppsqm_5y,\n       round(100.0 * p.median_ppsqm_5y / s.median_ppsqm_5y - 100, 1) AS parcel_vs_city_pct\nFROM parcels p\nJOIN gushim g ON g.gush = p.gush\nJOIN settlements s ON s.code = p.settlement_code\nWHERE p.gush = 6213 AND p.chelka = 1471","explanation":"The 5-year window (2021-07-01 to 2026-06-30) exists at every level: parcels, gushim and settlements. That makes it the fair common basis for a parcel that trades rarely.","tables":["parcels","gushim","settlements"],"verified":{"rows":1,"ms":0,"at":"2026-09-28"}},{"id":40,"slug":"bbox-parcels","category":"parcels-geo","title_en":"Parcels inside a map bounding box","title_he":"חלקות בתוך מלבן במפה","question":"Which parcels with deals are inside a small box in central Tel Aviv?","sql":"SELECT p.gush, p.chelka, p.lat, p.lon, p.deals_total, p.deals_5y, p.median_ppsqm_5y, p.last_deal_date, p.last_price\nFROM parcels_rtree r\nCROSS JOIN parcels p ON p.id = r.id\nWHERE r.minLat >= 32.0700 AND r.maxLat <= 32.0800\n  AND r.minLon >= 34.7700 AND r.maxLon <= 34.7850\nORDER BY p.deals_5y DESC\nLIMIT 100","explanation":"parcels_rtree is an R*Tree over parcel points (id = parcels.id). CROSS JOIN pins the loop order so the R*Tree drives the query. Settlement-centre fallback points are not in the tree, so a box never returns fake town-centre rows.","tables":["parcels_rtree","parcels"],"verified":{"rows":100,"ms":0.9,"at":"2026-09-28"}},{"id":41,"slug":"bbox-deals-12m","category":"parcels-geo","title_en":"Deals inside a bounding box","title_he":"עסקאות בתוך מלבן במפה","question":"List the residential deals of the last 12 months inside a box in central Tel Aviv.","sql":"SELECT d.id, d.deal_date, d.property_group, d.rooms, d.area, d.deal_amount, d.price_per_sqm,\n       d.gush, d.chelka, d.lat, d.lon, d.portion, d.is_outlier, d.is_multi_unit\nFROM parcels_rtree r\nCROSS JOIN parcels p ON p.id = r.id\nCROSS JOIN deals d ON d.gush = p.gush AND d.chelka = p.chelka\nWHERE r.minLat >= 32.0700 AND r.maxLat <= 32.0800\n  AND r.minLon >= 34.7700 AND r.maxLon <= 34.7850\n  AND d.deal_date BETWEEN '2025-07-01' AND '2026-06-30'\n  AND d.is_residential = 1\nORDER BY d.deal_date DESC, d.id DESC\nLIMIT 200","explanation":"Every deal of a parcel shares the parcel's point, so the fastest path is R*Tree, then parcels, then deals through ix_deals_gush_chelka. The deals_rtree view does the same join for you.","tables":["parcels_rtree","parcels","deals"],"verified":{"rows":200,"ms":2.2,"at":"2026-09-28"}},{"id":42,"slug":"nearest-parcels","category":"parcels-geo","title_en":"Nearest parcels to a point","title_he":"החלקות הקרובות לנקודה","question":"What are the 20 nearest parcels with deals to Dizengoff Center (32.0753, 34.7748), and how far are they?","sql":"SELECT p.gush, p.chelka, p.deals_total, p.median_ppsqm_5y, a.address_label,\n       round(6371000 * 2 * asin(sqrt(\n         pow(sin(radians(p.lat - 32.0753) / 2), 2) +\n         cos(radians(32.0753)) * cos(radians(p.lat)) * pow(sin(radians(p.lon - 34.7748) / 2), 2)\n       ))) AS distance_m\nFROM parcels_rtree r\nCROSS JOIN parcels p ON p.id = r.id\nLEFT JOIN parcel_address a ON a.gush = p.gush AND a.chelka = p.chelka\nWHERE r.minLat >= 32.0753 - 0.0045 AND r.maxLat <= 32.0753 + 0.0045\n  AND r.minLon >= 34.7748 - 0.0053 AND r.maxLon <= 34.7748 + 0.0053\nORDER BY distance_m\nLIMIT 20","explanation":"Prefilter with an R*Tree box (0.0045° of latitude is about 500 m; 0.0053° of longitude is about 500 m at latitude 32), then compute the exact haversine distance with the built-in math functions. Widen the box if you get fewer rows than you need.","tables":["parcels_rtree","parcels","parcel_address"],"verified":{"rows":20,"ms":1.6,"at":"2026-09-28"}},{"id":43,"slug":"radius-median-price","category":"parcels-geo","title_en":"Median price within a radius","title_he":"מחיר חציוני ברדיוס מנקודה","question":"What is the median price per m² of homes sold within 1 km of Dizengoff Center in the last 5 years?","sql":"SELECT count(*) AS n_deals,\n       round(median(d.price_per_sqm)) AS median_ppsqm,\n       round(percentile(d.price_per_sqm, 25)) AS p25_ppsqm,\n       round(percentile(d.price_per_sqm, 75)) AS p75_ppsqm\nFROM parcels_rtree r\nCROSS JOIN parcels p ON p.id = r.id\nCROSS JOIN deals d ON d.gush = p.gush AND d.chelka = p.chelka\nWHERE r.minLat >= 32.0753 - 0.009 AND r.maxLat <= 32.0753 + 0.009\n  AND r.minLon >= 34.7748 - 0.0106 AND r.maxLon <= 34.7748 + 0.0106\n  AND 6371000 * 2 * asin(sqrt(pow(sin(radians(p.lat - 32.0753) / 2), 2) + cos(radians(32.0753)) * cos(radians(p.lat)) * pow(sin(radians(p.lon - 34.7748) / 2), 2))) <= 1000\n  AND d.deal_date BETWEEN '2021-07-01' AND '2026-06-30'\n  AND d.in_stats = 1 AND d.price_per_sqm IS NOT NULL","explanation":"The box is a fast prefilter and the haversine condition turns it into a true circle. This SQLite build includes median() and percentile(). Apply the in_stats rule and a price_per_sqm IS NOT NULL filter to any price-per-m² median.","tables":["parcels_rtree","parcels","deals"],"verified":{"rows":1,"ms":7.4,"at":"2026-09-28"}},{"id":44,"slug":"deals-near-station","category":"parcels-geo","title_en":"Deals near a train station","title_he":"עסקאות ליד תחנת רכבת","question":"How much did homes sell for within 700 m of Tel Aviv Savidor station in the last 12 months?","sql":"WITH st AS (SELECT lat, lon FROM poi_stations WHERE id = 'rail:17038')\nSELECT count(*) AS n_deals, round(median(d.price_per_sqm)) AS median_ppsqm, round(median(d.deal_amount)) AS median_price\nFROM st\nCROSS JOIN parcels_rtree r\nCROSS JOIN parcels p ON p.id = r.id\nCROSS JOIN deals d ON d.gush = p.gush AND d.chelka = p.chelka\nWHERE r.minLat >= st.lat - 0.0063 AND r.maxLat <= st.lat + 0.0063\n  AND r.minLon >= st.lon - 0.0075 AND r.maxLon <= st.lon + 0.0075\n  AND 6371000 * 2 * asin(sqrt(pow(sin(radians(p.lat - st.lat) / 2), 2) + cos(radians(st.lat)) * cos(radians(p.lat)) * pow(sin(radians(p.lon - st.lon) / 2), 2))) <= 700\n  AND d.deal_date BETWEEN '2025-07-01' AND '2026-06-30'\n  AND d.in_stats = 1 AND d.price_per_sqm IS NOT NULL","explanation":"Take the station's coordinates from poi_stations (rail:17038 is תל אביב - סבידור מרכז; find ids by name first), then use the box-plus-haversine pattern. A one-row CTE avoids repeating the lookup.","tables":["poi_stations","parcels_rtree","parcels","deals"],"verified":{"rows":1,"ms":1.4,"at":"2026-09-28"}},{"id":45,"slug":"point-to-neighborhood","category":"parcels-geo","title_en":"Which neighbourhood is a point in?","title_he":"באיזו שכונה נמצאת נקודה?","question":"Which settlement and neighbourhood are at coordinates 31.7730, 35.2120?","sql":"SELECT p.gush, p.chelka, s.name AS settlement, n.name AS neighborhood, n.median_ppsqm_12m AS nbhd_ppsqm_12m,\n       round(6371000 * 2 * asin(sqrt(pow(sin(radians(p.lat - 31.7730) / 2), 2) + cos(radians(31.7730)) * cos(radians(p.lat)) * pow(sin(radians(p.lon - 35.2120) / 2), 2)))) AS distance_m\nFROM parcels_rtree r\nCROSS JOIN parcels p ON p.id = r.id\nLEFT JOIN settlements s ON s.code = p.settlement_code\nLEFT JOIN parcel_neighborhood pn ON pn.gush = p.gush AND pn.chelka = p.chelka\nLEFT JOIN neighborhoods n ON n.nbhd_id = pn.nbhd_id\nWHERE r.minLat >= 31.7730 - 0.003 AND r.maxLat <= 31.7730 + 0.003\n  AND r.minLon >= 35.2120 - 0.0035 AND r.maxLon <= 35.2120 + 0.0035\nORDER BY distance_m\nLIMIT 5","explanation":"The DB has no polygons, but every parcel has a point and a neighbourhood. The nearest parcel is a good proxy for \"where is this point\". Treat the answer as approximate near borders.","tables":["parcels_rtree","parcels","settlements","parcel_neighborhood","neighborhoods"],"verified":{"rows":5,"ms":0.4,"at":"2026-09-28"}},{"id":46,"slug":"settlements-within-radius","category":"parcels-geo","title_en":"Settlements within a radius","title_he":"יישובים ברדיוס מנקודה","question":"Which settlements lie within 15 km of Modiin, and what do homes cost there?","sql":"WITH c AS (SELECT lat, lon FROM settlements WHERE code = 1200)\nSELECT s.name, s.n_ppsqm_12m,\n       CASE WHEN s.n_ppsqm_12m >= 5 THEN s.median_ppsqm_12m END AS median_ppsqm_12m,\n       CASE WHEN s.n_stats_12m >= 5 THEN s.median_price_12m END AS median_price_12m,\n       round(6371 * 2 * asin(sqrt(pow(sin(radians(s.lat - c.lat) / 2), 2) + cos(radians(c.lat)) * cos(radians(s.lat)) * pow(sin(radians(s.lon - c.lon) / 2), 2))), 1) AS distance_km\nFROM settlements s, c\nWHERE s.code <> 1200\n  AND s.lat BETWEEN c.lat - 0.14 AND c.lat + 0.14\n  AND s.lon BETWEEN c.lon - 0.16 AND c.lon + 0.16\n  AND 6371 * 2 * asin(sqrt(pow(sin(radians(s.lat - c.lat) / 2), 2) + cos(radians(c.lat)) * cos(radians(s.lat)) * pow(sin(radians(s.lon - c.lon) / 2), 2))) <= 15\nORDER BY distance_km","explanation":"settlements has only 1,139 rows, so a scan with a distance filter is instant and no R*Tree is needed. The CASE expressions hide medians based on fewer than 5 deals, which are common in small villages.","tables":["settlements"],"verified":{"rows":73,"ms":0.3,"at":"2026-09-28"}},{"id":47,"slug":"settlement-search-prefix","category":"search","title_en":"Search settlements by name prefix","title_he":"חיפוש יישוב לפי תחילת השם","question":"Which settlements match \"באר\" (e.g. Be'er Sheva)?","sql":"SELECT s.code, s.name, s.name_en, s.deals_total\nFROM settlements_fts f\nJOIN settlements s ON s.code = f.rowid\nWHERE settlements_fts MATCH '\"באר\"*'\nORDER BY bm25(settlements_fts, 5.0, 1.0) - 2 * log(1 + s.deals_total)\nLIMIT 10","explanation":"settlements_fts indexes names and aliases (spellings, abbreviations, English names), with rowid = settlements.code. Quote each token and add * to the last one for prefix search. The ORDER BY mixes text relevance (name weighted 5×) with activity.","tables":["settlements_fts","settlements"],"verified":{"rows":9,"ms":0.1,"at":"2026-09-28"}},{"id":48,"slug":"settlement-search-abbreviation","category":"search","title_en":"Search settlements by abbreviation","title_he":"חיפוש יישוב לפי ראשי תיבות","question":"Which settlement does the abbreviation ת\"א refer to?","sql":"SELECT s.code, s.name, s.name_en, s.deals_total\nFROM settlements_fts f\nJOIN settlements s ON s.code = f.rowid\nWHERE settlements_fts MATCH '\"תא\"*'\nORDER BY bm25(settlements_fts, 5.0, 1.0) - 2 * log(1 + s.deals_total)\nLIMIT 10","explanation":"Strip quotes and geresh before matching (ת\"א becomes תא, ב\"ש becomes בש). The aliases column holds these squashed forms, so תל אביב-יפו ranks first.","tables":["settlements_fts","settlements"],"verified":{"rows":3,"ms":0.1,"at":"2026-09-28"}},{"id":49,"slug":"settlement-search-english","category":"search","title_en":"Search settlements in English","title_he":"חיפוש יישוב באנגלית","question":"Find the settlement called \"haifa\" in English.","sql":"SELECT s.code, s.name, s.name_en, s.district, s.deals_total\nFROM settlements_fts f\nJOIN settlements s ON s.code = f.rowid\nWHERE settlements_fts MATCH '\"haifa\"*'\nORDER BY bm25(settlements_fts, 5.0, 1.0) - 2 * log(1 + s.deals_total)\nLIMIT 5","explanation":"English CBS names are part of the aliases, and the unicode61 tokenizer is case-insensitive. Use this to map an English city name to its code before querying other tables.","tables":["settlements_fts","settlements"],"verified":{"rows":1,"ms":0,"at":"2026-09-28"}},{"id":50,"slug":"street-search","category":"search","title_en":"Search streets by name","title_he":"חיפוש רחוב לפי שם","question":"Which streets called Herzl (הרצל) exist, and where are they busiest?","sql":"SELECT s.id, s.street, s.settlement, s.deals_total, s.median_ppsqm_5y, s.n_ppsqm_5y\nFROM streets_fts f\nJOIN streets s ON s.id = f.rowid\nWHERE streets_fts MATCH '{street aliases} : \"הרצל\"*'\nORDER BY s.deals_total DESC\nLIMIT 20","explanation":"streets_fts is contentless (its columns read back NULL), so always join streets on rowid = streets.id. The column filter {street aliases} keeps a single word from matching settlement names: הרצל would otherwise return every street in הרצלייה.","tables":["streets_fts","streets"],"verified":{"rows":20,"ms":0.1,"at":"2026-09-28"}},{"id":51,"slug":"street-in-city-search","category":"search","title_en":"Search a street within a city","title_he":"חיפוש רחוב בתוך עיר","question":"Find Rothschild street in Tel Aviv (query \"רוטשילד ת\"א\").","sql":"SELECT s.id, s.street, s.settlement, s.deals_total, s.median_ppsqm_5y, s.house_min, s.house_max\nFROM streets_fts f\nJOIN streets s ON s.id = f.rowid\nWHERE streets_fts MATCH '({street aliases} : \"רוטשילד\" OR {settlement} : \"רוטשילד\") AND ({street aliases} : \"תא\"* OR {settlement} : \"תא\"*)'\nORDER BY s.deals_total DESC\nLIMIT 10","explanation":"For multi-word input, let each word match either the street or the settlement column, and make the last word a prefix. The settlement column holds the city name plus its Hebrew abbreviations (תא, בש, ...).","tables":["streets_fts","streets"],"verified":{"rows":4,"ms":0.2,"at":"2026-09-28"}},{"id":52,"slug":"neighborhood-search","category":"search","title_en":"Search neighbourhoods by name","title_he":"חיפוש שכונה לפי שם","question":"Find the neighbourhood Florentin (פלורנטין) and its prices.","sql":"SELECT n.nbhd_id, n.name, n.settlement, n.deals_12m, n.median_ppsqm_12m, n.n_ppsqm_12m, n.ppsqm_vs_settlement_pct\nFROM neighborhoods_fts f\nJOIN neighborhoods n ON n.nbhd_id = f.rowid\nWHERE neighborhoods_fts MATCH '{name aliases} : \"פלורנטין\"*'\nORDER BY n.deals_total DESC\nLIMIT 10","explanation":"neighborhoods_fts is contentless with rowid = neighborhoods.nbhd_id. Use the returned nbhd_id with parcel_neighborhood and agg_neighborhood_year.","tables":["neighborhoods_fts","neighborhoods"],"verified":{"rows":1,"ms":0,"at":"2026-09-28"}},{"id":53,"slug":"city-neighborhoods-ranked","category":"neighborhoods-streets","title_en":"Neighbourhoods of a city by price","title_he":"שכונות העיר לפי מחיר","question":"Rank the neighbourhoods of Tel Aviv by median price per m².","sql":"SELECT nbhd_id, name, deals_12m, n_ppsqm_12m, median_ppsqm_12m, median_ppsqm_existing_12m,\n       ppsqm_change_pct, rank_ppsqm_in_settlement, ppsqm_vs_settlement_pct, max_parcel_share_12m\nFROM neighborhoods\nWHERE settlement_code = 5000 AND n_ppsqm_12m >= 20\nORDER BY median_ppsqm_12m DESC","explanation":"Neighbourhood medians follow the settlement rules (in_stats, 12 months to the anchor). Gate them: n >= 20 here, and show a median at all only when n >= 5. A high max_parcel_share_12m means one building dominates the median.","tables":["neighborhoods"],"verified":{"rows":35,"ms":0.1,"at":"2026-09-28"}},{"id":54,"slug":"neighborhood-yearly-series","category":"neighborhoods-streets","title_en":"Yearly series of a neighbourhood","title_he":"סדרה שנתית של שכונה","question":"How have prices in Rehavia (Jerusalem) evolved year by year?","sql":"SELECT a.year, a.deals, a.n_ppsqm, a.median_ppsqm, a.n_ppsqm_existing, a.median_ppsqm_existing, a.is_incomplete\nFROM neighborhoods n\nJOIN agg_neighborhood_year a ON a.nbhd_id = n.nbhd_id\nWHERE n.settlement_code = 3000 AND n.name = 'רחביה'\nORDER BY a.year","explanation":"Look up the neighbourhood by (settlement_code, name) or by nbhd_id from the FTS search. agg_neighborhood_year also has the existing-stock median, which is steadier when a new project dominates a year.","tables":["neighborhoods","agg_neighborhood_year"],"verified":{"rows":29,"ms":0.1,"at":"2026-09-28"}},{"id":55,"slug":"neighborhood-risers","category":"neighborhoods-streets","title_en":"Neighbourhoods with the biggest price change","title_he":"השכונות עם השינוי הגדול במחיר","question":"Which neighbourhoods in Israel had the largest existing-stock price rise over the last year?","sql":"SELECT settlement, name, ppsqm_change_pct, median_ppsqm_existing_prev12m, median_ppsqm_existing_12m,\n       n_ppsqm_existing_12m, n_ppsqm_existing_prev12m\nFROM neighborhoods\nWHERE ppsqm_change_pct IS NOT NULL\nORDER BY ppsqm_change_pct DESC\nLIMIT 20","explanation":"ppsqm_change_pct is only set when both windows have at least 50 existing-stock deals (194 neighbourhoods), so it is never based on a tiny sample. Sort ASC for the biggest falls.","tables":["neighborhoods"],"verified":{"rows":20,"ms":0.2,"at":"2026-09-28"}},{"id":56,"slug":"neighborhood-latest-deals","category":"neighborhoods-streets","title_en":"Latest deals in a neighbourhood","title_he":"העסקאות האחרונות בשכונה","question":"Show the 50 latest deals in Florentin, Tel Aviv.","sql":"SELECT d.id, d.deal_date, d.property_group, d.rooms, d.area, d.deal_amount, d.price_per_sqm,\n       d.gush, d.chelka, d.portion, d.is_outlier, d.is_multi_unit\nFROM neighborhoods n\nCROSS JOIN parcel_neighborhood pn ON pn.nbhd_id = n.nbhd_id\nCROSS JOIN deals d ON d.gush = pn.gush AND d.chelka = pn.chelka\nWHERE n.settlement_code = 5000 AND n.name = 'פלורנטין'\n  AND d.settlement_code = n.settlement_code\nORDER BY d.deal_date DESC, d.id DESC\nLIMIT 50","explanation":"parcel_neighborhood maps each parcel to its neighbourhood. CROSS JOIN pins the loop order (neighbourhood, then its parcels, then deals by (gush, chelka)). The settlement_code condition matches how the neighbourhood statistics are computed.","tables":["neighborhoods","parcel_neighborhood","deals"],"verified":{"rows":50,"ms":0.9,"at":"2026-09-28"}},{"id":57,"slug":"priciest-streets","category":"neighborhoods-streets","title_en":"Most expensive streets of a city","title_he":"הרחובות היקרים בעיר","question":"Which streets in Tel Aviv have the highest median price per m² over 5 years?","sql":"SELECT id, street, deals_5y, n_ppsqm_5y, median_ppsqm_5y, n_ppsqm_12m, median_ppsqm_12m, last_deal_date\nFROM streets\nWHERE settlement_code = 5000 AND n_ppsqm_5y >= 20\nORDER BY median_ppsqm_5y DESC\nLIMIT 25","explanation":"Street statistics cover the parcels with an OSM address on the street or next to it, so a corner parcel counts on each of its streets. Streets have fewer deals than neighbourhoods, so the 5-year window with an n gate is the safer basis.","tables":["streets"],"verified":{"rows":25,"ms":1,"at":"2026-09-28"}},{"id":58,"slug":"street-deals","category":"neighborhoods-streets","title_en":"Deals on a street","title_he":"עסקאות ברחוב","question":"What sold on Rothschild street in Tel Aviv recently, with house numbers?","sql":"SELECT d.id, d.deal_date, d.property_group, d.rooms, d.area, d.deal_amount, d.price_per_sqm,\n       a.house_numbers, sp.kind, d.portion, d.is_outlier\nFROM streets s\nCROSS JOIN street_parcels sp ON sp.street_id = s.id\nCROSS JOIN deals d ON d.gush = sp.gush AND d.chelka = sp.chelka\nLEFT JOIN parcel_address a ON a.gush = sp.gush AND a.chelka = sp.chelka\nWHERE s.settlement_code = 5000 AND s.street = 'רוטשילד'\nORDER BY d.deal_date DESC, d.id DESC\nLIMIT 50","explanation":"street_parcels lists the parcels of each street. kind = 'near' means the parcel is only next to the street, so say \"near\" rather than \"on\". Addresses come from OpenStreetMap, not from the registry.","tables":["streets","street_parcels","deals","parcel_address"],"verified":{"rows":50,"ms":1.6,"at":"2026-09-28"}},{"id":59,"slug":"neighborhood-premium","category":"neighborhoods-streets","title_en":"Neighbourhood premium vs its city","title_he":"פרמיית השכונה ביחס לעיר","question":"Which Jerusalem neighbourhoods are most above or below the city's price per m²?","sql":"SELECT name, median_ppsqm_12m, n_ppsqm_12m, ppsqm_vs_settlement_pct, rank_ppsqm_in_settlement, ranked_in_settlement\nFROM neighborhoods\nWHERE settlement_code = 3000 AND ppsqm_vs_settlement_pct IS NOT NULL\nORDER BY ppsqm_vs_settlement_pct DESC","explanation":"ppsqm_vs_settlement_pct is precomputed only when the neighbourhood has at least 20 and the city at least 30 deals. A NULL rank means unranked: an industrial or business zone (which can still hold new housing) or too few parcels.","tables":["neighborhoods"],"verified":{"rows":43,"ms":0.1,"at":"2026-09-28"}},{"id":60,"slug":"rail-distance-price","category":"location","title_en":"Price vs distance to a train station","title_he":"מחיר מול מרחק מתחנת רכבת","question":"In Netanya, do homes closer to a train station sell for more per m²?","sql":"SELECT CASE WHEN m.dist_rail_m < 1000 THEN '1: < 1 km'\n            WHEN m.dist_rail_m < 2000 THEN '2: 1-2 km'\n            WHEN m.dist_rail_m < 3000 THEN '3: 2-3 km'\n            ELSE '4: 3+ km' END AS rail_distance,\n       count(*) AS n_deals,\n       round(median(d.price_per_sqm)) AS median_ppsqm\nFROM deals d\nJOIN loc_metrics_parcel m ON m.gush = d.gush AND m.chelka = d.chelka\nWHERE d.settlement_code = 7400\n  AND d.deal_date BETWEEN '2021-07-01' AND '2026-06-30'\n  AND d.in_stats = 1 AND d.price_per_sqm IS NOT NULL\n  AND d.property_group = 'apartment'\nGROUP BY rail_distance\nORDER BY rail_distance","explanation":"loc_metrics_parcel has straight-line distances from each parcel point to the nearest operating station. Compare within one city and one property type, otherwise location effects mix with city price levels. This shows correlation, not causation.","tables":["deals","loc_metrics_parcel"],"verified":{"rows":4,"ms":61.1,"at":"2026-09-28"}},{"id":61,"slug":"sea-distance-price","category":"location","title_en":"Price vs distance to the sea","title_he":"מחיר מול מרחק מהים","question":"How much more do apartments near the beach cost in Bat Yam?","sql":"SELECT CASE WHEN m.dist_sea_m < 500 THEN '1: < 500 m'\n            WHEN m.dist_sea_m < 1000 THEN '2: 500 m-1 km'\n            WHEN m.dist_sea_m < 2000 THEN '3: 1-2 km'\n            ELSE '4: 2+ km' END AS sea_distance,\n       count(*) AS n_deals,\n       round(median(d.price_per_sqm)) AS median_ppsqm,\n       round(median(d.deal_amount)) AS median_price\nFROM deals d\nJOIN loc_metrics_parcel m ON m.gush = d.gush AND m.chelka = d.chelka\nWHERE d.settlement_code = 6200\n  AND d.property_group = 'apartment'\n  AND d.deal_date BETWEEN '2021-07-01' AND '2026-06-30'\n  AND d.in_stats = 1 AND d.price_per_sqm IS NOT NULL\nGROUP BY sea_distance\nORDER BY sea_distance","explanation":"dist_sea_m is the distance to the OSM coastline (Mediterranean or Red Sea). Bucketing and taking median() per bucket over deal-level rows is the correct way to compare. Never average precomputed medians.","tables":["deals","loc_metrics_parcel"],"verified":{"rows":4,"ms":14.2,"at":"2026-09-28"}},{"id":62,"slug":"schools-nearby-price","category":"location","title_en":"Price vs elementary schools nearby","title_he":"מחיר מול מספר בתי ספר יסודיים בקרבת מקום","question":"In Jerusalem, how does price per m² vary with the number of elementary schools within 1 km?","sql":"SELECT CASE WHEN m.elementary_1km = 0 THEN '0'\n            WHEN m.elementary_1km <= 2 THEN '1-2'\n            WHEN m.elementary_1km <= 5 THEN '3-5'\n            ELSE '6+' END AS elementary_schools_1km,\n       count(*) AS n_deals,\n       round(median(d.price_per_sqm)) AS median_ppsqm\nFROM deals d\nJOIN loc_metrics_parcel m ON m.gush = d.gush AND m.chelka = d.chelka\nWHERE d.settlement_code = 3000\n  AND d.deal_date BETWEEN '2021-07-01' AND '2026-06-30'\n  AND d.in_stats = 1 AND d.price_per_sqm IS NOT NULL\nGROUP BY elementary_schools_1km\nORDER BY min(m.elementary_1km)","explanation":"School counts leave out institutions on placeholder coordinates (loc_placeholder = 1), so they are lower bounds in some Arab towns and East Jerusalem. School density also tracks population density, so read this as descriptive.","tables":["deals","loc_metrics_parcel"],"verified":{"rows":4,"ms":44.7,"at":"2026-09-28"}},{"id":63,"slug":"noise-zone-price","category":"location","title_en":"Homes inside airport noise zones","title_he":"דירות באזורי רעש מטוסים","question":"Which settlements have home sales inside airport noise zones, and at what prices compared with the rest of the town?","sql":"WITH z AS (\n  SELECT d.settlement_code, count(*) AS n_in_zone,\n         round(median(d.price_per_sqm)) AS median_ppsqm_in_zone,\n         group_concat(DISTINCT m.noise_zone) AS zones\n  FROM loc_metrics_parcel m\n  CROSS JOIN deals d ON d.gush = m.gush AND d.chelka = m.chelka\n  WHERE m.noise_zone IS NOT NULL\n    AND d.deal_date BETWEEN '2021-07-01' AND '2026-06-30'\n    AND d.in_stats = 1 AND d.price_per_sqm IS NOT NULL\n  GROUP BY d.settlement_code\n  HAVING count(*) >= 20\n)\nSELECT s.name, z.zones, z.n_in_zone, z.median_ppsqm_in_zone,\n       s.median_ppsqm_5y AS town_median_ppsqm_5y,\n       round(100.0 * z.median_ppsqm_in_zone / s.median_ppsqm_5y - 100, 1) AS in_zone_vs_town_pct\nFROM z\nJOIN settlements s ON s.code = z.settlement_code\nORDER BY z.n_in_zone DESC","explanation":"noise_zone has no index, but loc_metrics_parcel (381k rows) scans in about 40 ms, and only the 1.6% of parcels inside an active airport noise zone then reach their deals through ix_deals_gush_chelka. Compare with the town's 5-year median over the same window, keeping in mind that in-zone parcels can be a very different part of town (in Tel Aviv they are in the south-east: Kfar Shalem, Hatikva).","tables":["loc_metrics_parcel","deals","settlements"],"verified":{"rows":8,"ms":42.5,"at":"2026-09-28"}},{"id":64,"slug":"socio-cluster-price","category":"location","title_en":"Price by socio-economic cluster","title_he":"מחיר לפי אשכול חברתי-כלכלי","question":"How does the price per m² vary with the CBS socio-economic cluster of the settlement?","sql":"SELECT ss.cluster, count(DISTINCT ss.settlement_code) AS settlements, count(*) AS n_deals,\n       round(median(d.price_per_sqm)) AS median_ppsqm, round(median(d.deal_amount)) AS median_price\nFROM settlement_socio ss\nJOIN deals d ON d.settlement_code = ss.settlement_code\nWHERE d.deal_date BETWEEN '2025-07-01' AND '2026-06-30'\n  AND d.in_stats = 1 AND d.price_per_sqm IS NOT NULL\nGROUP BY ss.cluster\nORDER BY ss.cluster","explanation":"This takes a true median over deal-level rows per cluster (1 = lowest, 10 = highest, CBS 2021), not an average of settlement medians. Clusters mix very different towns: Bnei Brak and Jerusalem are in cluster 2 but have high prices.","tables":["settlement_socio","deals"],"verified":{"rows":9,"ms":75.7,"at":"2026-09-28"}},{"id":65,"slug":"crime-vs-price","category":"location","title_en":"Crime rate vs price in big cities","title_he":"שיעור הפשיעה מול מחירים בערים הגדולות","question":"For cities over 100k residents, how does the 2025 crime rate compare with home prices?","sql":"SELECT s.name, c.per_1000, c.per_1000_index, c.cases, s.median_ppsqm_12m, ss.cluster AS socio_cluster\nFROM crime_settlement_year c\nJOIN settlements s ON s.code = c.settlement_code\nLEFT JOIN settlement_socio ss ON ss.settlement_code = c.settlement_code\nWHERE c.year = 2025 AND s.population >= 100000\nORDER BY c.per_1000_index DESC","explanation":"per_1000_index is the case rate relative to the national rate of the same year (100 = national). Use it to compare across years, because the 2021-2022 police files hold about half the cases of later years.","tables":["crime_settlement_year","settlements","settlement_socio"],"verified":{"rows":21,"ms":0.2,"at":"2026-09-28"}},{"id":66,"slug":"transit-score-price","category":"location","title_en":"Price vs public-transport score","title_he":"מחיר מול ציון תחבורה ציבורית","question":"In Jerusalem, do homes with better public transport sell for more?","sql":"SELECT (m.transit_score / 10) * 10 AS transit_score_from,\n       count(*) AS n_deals,\n       round(median(d.price_per_sqm)) AS median_ppsqm\nFROM deals d\nJOIN loc_metrics_parcel m ON m.gush = d.gush AND m.chelka = d.chelka\nWHERE d.settlement_code = 3000\n  AND d.deal_date BETWEEN '2021-07-01' AND '2026-06-30'\n  AND d.in_stats = 1 AND d.price_per_sqm IS NOT NULL\nGROUP BY transit_score_from\nHAVING count(*) >= 30\nORDER BY transit_score_from","explanation":"transit_score (0-100) combines weekday bus trips within 500 m with nearby rail and light-rail service. Integer division (score / 10) * 10 makes 10-point bands, and HAVING drops bands with too few deals.","tables":["deals","loc_metrics_parcel"],"verified":{"rows":6,"ms":42.6,"at":"2026-09-28"}},{"id":67,"slug":"parcel-quality-of-life","category":"location","title_en":"Parcel quality-of-life panel","title_he":"לוח איכות חיים לחלקה","question":"For parcel 6212/418 in Tel Aviv, what is its address, neighbourhood, transit, schools, sea distance and socio-economic level?","sql":"SELECT p.gush, p.chelka, a.address_label, a.street_source,\n       n.name AS neighborhood, n.median_ppsqm_12m AS nbhd_ppsqm_12m,\n       m.transit_score, m.dist_rail_m, rs.name AS rail_station, m.rail_weekday_trips,\n       m.dist_lrt_m, ls.name AS lrt_station, m.bus_lines_500m,\n       m.elementary_1km, m.dist_elementary_m, m.kindergartens_500m,\n       m.dist_sea_m, m.elevation_m, m.noise_zone,\n       sa.cluster AS stat_area_cluster, ss.cluster AS settlement_cluster,\n       rc.name AS renewal_compound, rc.status AS renewal_status\nFROM parcels p\nLEFT JOIN parcel_address a ON a.gush = p.gush AND a.chelka = p.chelka\nLEFT JOIN parcel_neighborhood pn ON pn.gush = p.gush AND pn.chelka = p.chelka\nLEFT JOIN neighborhoods n ON n.nbhd_id = pn.nbhd_id\nLEFT JOIN loc_metrics_parcel m ON m.gush = p.gush AND m.chelka = p.chelka\nLEFT JOIN poi_stations rs ON rs.id = m.rail_station_id\nLEFT JOIN poi_stations ls ON ls.id = m.lrt_station_id\nLEFT JOIN parcel_stat_area_socio sa ON sa.gush = p.gush AND sa.chelka = p.chelka\nLEFT JOIN settlement_socio ss ON ss.settlement_code = p.settlement_code\nLEFT JOIN parcel_renewal r ON r.gush = p.gush AND r.chelka = p.chelka\nLEFT JOIN renewal_compounds rc ON rc.compound_id = r.compound_id\nWHERE p.gush = 6212 AND p.chelka = 418","explanation":"Every enrichment table keys on (gush, chelka), settlement_code or an id, so one row can carry them all through LEFT JOINs (a missing match is NULL, not an error). Distances are straight lines in metres.","tables":["parcels","parcel_address","parcel_neighborhood","neighborhoods","loc_metrics_parcel","poi_stations","parcel_stat_area_socio","settlement_socio","parcel_renewal","renewal_compounds"],"verified":{"rows":1,"ms":0,"at":"2026-09-28"}},{"id":68,"slug":"real-prices-yearly","category":"market","title_en":"National prices in today's shekels","title_he":"מחירים ארציים בשקלים של היום","question":"What was the national median apartment price each year in June-2026 shekels?","sql":"SELECT a.year, a.median_price AS median_price_nominal,\n       round(a.median_price * f.rf) AS median_price_real,\n       a.median_ppsqm AS median_ppsqm_nominal,\n       round(a.median_ppsqm * f.rf) AS median_ppsqm_real\nFROM agg_national_year a\nJOIN (SELECT CAST(substr(month, 1, 4) AS INTEGER) AS year, avg(real_factor) AS rf\n      FROM macro_month GROUP BY 1) f ON f.year = a.year\nWHERE a.property_group = 'apartment' AND a.rooms_bucket = 'all' AND a.is_incomplete = 0\nORDER BY a.year","explanation":"macro_month.real_factor converts nominal shekels of a month into June-2026 shekels (CPI based). For yearly figures, use the year's mean factor. The real series shows how much of the nominal rise is inflation.","tables":["agg_national_year","macro_month"],"verified":{"rows":28,"ms":0.2,"at":"2026-09-28"}},{"id":69,"slug":"real-vs-nominal-monthly","category":"market","title_en":"Monthly real vs nominal price","title_he":"מחיר ריאלי מול נומינלי לפי חודש","question":"Show the monthly national residential price per m², nominal and inflation-adjusted, since 2016.","sql":"SELECT a.month, a.median_ppsqm AS nominal, round(a.median_ppsqm * m.real_factor) AS real_2026_06, a.is_incomplete\nFROM agg_national_month a\nJOIN macro_month m ON m.month = a.month\nWHERE a.property_group = 'all_residential' AND a.month >= '2016-01'\nORDER BY a.month","explanation":"agg_national_month.month and macro_month.month share the 'YYYY-MM' format, so they join directly. real_factor is NULL for months without a CPI yet.","tables":["agg_national_month","macro_month"],"verified":{"rows":129,"ms":0.1,"at":"2026-09-28"}},{"id":70,"slug":"mortgage-rates-vs-prices","category":"market","title_en":"Mortgage rates vs prices and activity","title_he":"ריבית משכנתאות מול מחירים ופעילות","question":"How did mortgage rates relate to apartment prices and deal volume year by year?","sql":"SELECT a.year, a.deals AS apartment_deals, a.median_price,\n       round(r.boi_rate, 2) AS boi_rate, round(r.mortgage_unlinked, 2) AS mortgage_rate_unlinked,\n       round(r.mortgage_linked, 2) AS mortgage_rate_linked\nFROM agg_national_year a\nJOIN (SELECT CAST(substr(month, 1, 4) AS INTEGER) AS year,\n             avg(boi_rate) AS boi_rate,\n             avg(mortgage_rate_unlinked) AS mortgage_unlinked,\n             avg(mortgage_rate_linked) AS mortgage_linked\n      FROM macro_month GROUP BY 1) r ON r.year = a.year\nWHERE a.property_group = 'apartment' AND a.rooms_bucket = 'all' AND a.year BETWEEN 2012 AND 2025\nORDER BY a.year","explanation":"Mortgage rates start in 2011-07, and yearly averages of monthly rates are fine to compute. The prime-track rate (mortgage_rate_variable_unlinked) only covers 2016-01 to 2024-01; use prime_rate as the benchmark after that.","tables":["agg_national_year","macro_month"],"verified":{"rows":14,"ms":0.2,"at":"2026-09-28"}},{"id":71,"slug":"rent-series-city","category":"market","title_en":"Rent series of a city","title_he":"סדרת שכר דירה של עיר","question":"How has average rent in Tel Aviv changed by quarter and apartment size?","sql":"SELECT period,\n       max(CASE WHEN rooms_bucket = '1-2' THEN avg_rent END) AS rooms_1_2,\n       max(CASE WHEN rooms_bucket = '2.5-3' THEN avg_rent END) AS rooms_2_5_3,\n       max(CASE WHEN rooms_bucket = '3.5-4' THEN avg_rent END) AS rooms_3_5_4,\n       max(CASE WHEN rooms_bucket IN ('4.5-6', '4.5+') THEN avg_rent END) AS rooms_4_5_plus,\n       max(CASE WHEN rooms_bucket = 'all' THEN avg_rent END) AS all_rooms\nFROM rent_city\nWHERE settlement_code = 5000 AND period_type = 'quarter'\nGROUP BY period\nORDER BY period","explanation":"rent_city holds CBS average rents of current tenancies (not asking rents) for 18 big cities. The top bucket was renamed from 4.5-6 to 4.5+ in 2026, which is why both are mapped to one column.","tables":["rent_city"],"verified":{"rows":30,"ms":0.3,"at":"2026-09-28"}},{"id":72,"slug":"gross-yield-cities","category":"market","title_en":"Gross rental yield by city","title_he":"תשואה ברוטו משכירות לפי עיר","question":"Which big cities have the highest gross rental yield?","sql":"SELECT area_name, settlement_code, n_deals, median_price, round(avg_rent_monthly) AS avg_rent_monthly,\n       price_to_rent, gross_yield_pct, low_n\nFROM gross_yield\nWHERE level = 'city' AND window = '12m' AND rooms_bucket = 'all'\nORDER BY gross_yield_pct DESC","explanation":"gross_yield compares the median existing-stock apartment price (12 months to the anchor) with 12 × the CBS average rent. It is indicative only, because the rent comes from other homes than the ones sold.","tables":["gross_yield"],"verified":{"rows":18,"ms":0.1,"at":"2026-09-28"}},{"id":73,"slug":"affordability-trend","category":"market","title_en":"Affordability: years of wages per apartment","title_he":"נגישות לדיור: שנות שכר לדירה","question":"How many years of the average wage does a median apartment cost, over time?","sql":"SELECT period, median_price_apartment, round(avg_monthly_wage) AS avg_monthly_wage, months_of_wage, years_of_wage\nFROM affordability\nWHERE level = 'national' AND stock = 'all'\nORDER BY period_kind = '12m', period","explanation":"affordability divides the median apartment price by the national average monthly wage. Rows are yearly plus a '12m' row for the window to the stats anchor. District rows also use the national wage.","tables":["affordability"],"verified":{"rows":29,"ms":0,"at":"2026-09-28"}},{"id":74,"slug":"cbs-index-vs-registry","category":"market","title_en":"CBS price index vs registry medians","title_he":"מדד מחירי הדירות של הלמ״ס מול החציון במרשם","question":"Does the CBS dwelling price index move like the registry median? Compare both, rebased to 2015 = 100.","sql":"WITH idx AS (\n  SELECT CAST(substr(month, 1, 4) AS INTEGER) AS year, avg(housing_price_index) AS hpi\n  FROM macro_month GROUP BY 1\n),\nreg AS (\n  SELECT year, median_ppsqm FROM agg_national_year\n  WHERE property_group = 'apartment' AND rooms_bucket = 'all' AND is_incomplete = 0\n)\nSELECT reg.year,\n       round(100.0 * reg.median_ppsqm / (SELECT median_ppsqm FROM reg WHERE year = 2015), 1) AS registry_ppsqm_2015_100,\n       round(100.0 * idx.hpi / (SELECT hpi FROM idx WHERE year = 2015), 1) AS cbs_index_2015_100\nFROM reg JOIN idx ON idx.year = reg.year\nWHERE reg.year >= 2008\nORDER BY reg.year","explanation":"The CBS index is quality-adjusted (it controls for what kind of dwellings sold), while the registry median moves with the mix of what sold, so gaps between the two lines are expected. The CBS month label is the first month of a two-month deal window.","tables":["macro_month","agg_national_year"],"verified":{"rows":18,"ms":0.2,"at":"2026-09-28"}},{"id":75,"slug":"deal-history-real-prices","category":"market","title_en":"A parcel's deal history in today's shekels","title_he":"היסטוריית עסקאות בחלקה בשקלים של היום","question":"Show the home sales in parcel 7016/25 (Tel Aviv) since 1998 with their prices converted to June-2026 shekels.","sql":"SELECT d.id, d.deal_date, d.sub_chelka, d.rooms, d.area, d.deal_amount,\n       round(d.deal_amount * m.real_factor) AS deal_amount_real,\n       round(d.price_per_sqm * m.real_factor) AS ppsqm_real\nFROM deals d\nJOIN macro_month m ON m.month = substr(d.deal_date, 1, 7)\nWHERE d.gush = 7016 AND d.chelka = 25 AND d.is_residential = 1 AND d.is_full_deal = 1\nORDER BY d.deal_date","explanation":"Join a single deal to its month's factor with substr(deal_date, 1, 7). The macro_month primary key makes this a point lookup per row.","tables":["deals","macro_month"],"verified":{"rows":29,"ms":0.1,"at":"2026-09-28"}},{"id":76,"slug":"renewal-compounds-city","category":"projects","title_en":"Urban renewal compounds in a city","title_he":"מתחמי התחדשות עירונית בעיר","question":"What declared urban-renewal compounds exist in Bat Yam, and how advanced are they?","sql":"SELECT compound_id, name, track, status, status_rank, units_existing, units_planned, plan_number, mavat_url\nFROM renewal_compounds\nWHERE settlement_code = 6200\nORDER BY status_rank DESC, units_planned DESC","explanation":"status_rank orders the planning stages from 1 (initial planning) to 5 (approved plan in execution). Only declared compounds are included (פינוי-בינוי, עיבוי), not תמ\"א 38 building projects.","tables":["renewal_compounds"],"verified":{"rows":41,"ms":0.1,"at":"2026-09-28"}},{"id":77,"slug":"largest-renewal-compounds","category":"projects","title_en":"Largest urban renewal compounds","title_he":"מתחמי ההתחדשות הגדולים ביותר","question":"Which urban renewal compounds in Israel will add the most housing units?","sql":"SELECT settlement_name, name, track, status, units_existing, units_planned,\n       units_planned - units_existing AS net_new_units\nFROM renewal_compounds\nWHERE units_planned IS NOT NULL AND units_existing IS NOT NULL\nORDER BY net_new_units DESC\nLIMIT 20","explanation":"units_planned is the total after renewal and units_existing is what gets demolished or reinforced, so the difference is the net addition. The table has 978 rows, so a scan is instant.","tables":["renewal_compounds"],"verified":{"rows":20,"ms":0.2,"at":"2026-09-28"}},{"id":78,"slug":"renewal-vs-other-prices","category":"projects","title_en":"Prices on renewal parcels vs elsewhere","title_he":"מחירים בחלקות התחדשות מול שאר העיר","question":"In Bat Yam, do existing apartments on urban-renewal parcels sell at a premium?","sql":"SELECT CASE WHEN r.compound_id IS NULL THEN 'not in a compound'\n            WHEN rc.status_rank >= 3 THEN 'compound, plan approved'\n            ELSE 'compound, still planning' END AS renewal,\n       count(*) AS n_deals,\n       round(median(d.price_per_sqm)) AS median_ppsqm,\n       round(median(d.deal_amount)) AS median_price\nFROM deals d\nLEFT JOIN parcel_renewal r ON r.gush = d.gush AND r.chelka = d.chelka\nLEFT JOIN renewal_compounds rc ON rc.compound_id = r.compound_id\nWHERE d.settlement_code = 6200\n  AND d.deal_date BETWEEN '2021-07-01' AND '2026-06-30'\n  AND d.property_group = 'apartment' AND d.is_new_build = 0\n  AND d.in_stats = 1 AND d.price_per_sqm IS NOT NULL\nGROUP BY renewal\nORDER BY median_ppsqm DESC","explanation":"is_new_build = 0 keeps old units that are waiting for renewal, rather than the new towers built on the same parcels. parcel_renewal maps parcels to compounds, and a LEFT JOIN labels the rest.","tables":["deals","parcel_renewal","renewal_compounds"],"verified":{"rows":3,"ms":9.1,"at":"2026-09-28"}},{"id":79,"slug":"discount-projects-city","category":"projects","title_en":"Discount lottery projects in a city","title_he":"פרויקטים של דירה בהנחה בעיר","question":"Which subsidised lottery projects (מחיר למשתכן / דירה בהנחה) were in Beit Shemesh?","sql":"SELECT project_id, program, name, developer, units, winners_total, price_per_sqm, lottery_date, n_deals_official\nFROM discount_projects\nWHERE settlement_code = 2610\nORDER BY lottery_date DESC\nLIMIT 50","explanation":"discount_projects lists official lottery projects (2016-2025) with their official price per m². n_deals_official is the number of registry deals attributed to the project, capped at its number of units; recent lotteries show 0 because sales are registered years later.","tables":["discount_projects"],"verified":{"rows":50,"ms":0.1,"at":"2026-09-28"}},{"id":80,"slug":"discount-vs-market","category":"projects","title_en":"Discount projects vs the market price","title_he":"פרויקטי הנחה מול מחיר השוק","question":"How far below the market were the biggest discount-lottery projects sold?","sql":"WITH pd AS (\n  SELECT d.discount_project_id AS project_id, count(*) AS n, round(median(d.price_per_sqm)) AS ppsqm, min(d.year) AS first_year\n  FROM deals d\n  WHERE d.discount_project_id IN (SELECT project_id FROM discount_projects WHERE n_deals_official >= 150)\n    AND d.price_per_sqm IS NOT NULL\n  GROUP BY d.discount_project_id\n)\nSELECT p.project_id, p.settlement_name, p.name, p.program, pd.n, pd.first_year,\n       pd.ppsqm AS project_ppsqm, a.median_ppsqm AS town_ppsqm_same_year,\n       round(100.0 * pd.ppsqm / a.median_ppsqm - 100, 1) AS discount_pct\nFROM pd\nJOIN discount_projects p ON p.project_id = pd.project_id\nJOIN agg_settlement_year a ON a.settlement_code = p.settlement_code AND a.property_group = 'all_residential' AND a.year = pd.first_year\nORDER BY discount_pct\nLIMIT 30","explanation":"Discount-project deals are never in_stats, so query them through discount_project_id (partial index ix_deals_discount_project). Compare them with the town's in-stats median of the same year, which already excludes those deals.","tables":["deals","discount_projects","agg_settlement_year"],"verified":{"rows":30,"ms":8.9,"at":"2026-09-28"}},{"id":81,"slug":"discount-project-deals","category":"projects","title_en":"Deals of one discount project","title_he":"העסקאות של פרויקט הנחה אחד","question":"Show the registry deals attributed to discount project moch:52 (מחיר למשתכן in Ramla).","sql":"SELECT id, deal_date, gush, chelka, rooms, area, deal_amount, price_per_sqm, year_built\nFROM deals\nWHERE discount_project_id = 'moch:52'\nORDER BY deal_date DESC\nLIMIT 100","explanation":"Attribution is by parcel, date window and price, not by buyer, so describe these as \"deals attributed to the project\". Show them with a \"reduced price\" badge, never as market prices.","tables":["deals"],"verified":{"rows":100,"ms":0.3,"at":"2026-09-28"}},{"id":82,"slug":"in-stats-breakdown","category":"data-quality","title_en":"Why rows are excluded from statistics","title_he":"מדוע שורות לא נכנסות לסטטיסטיקה","question":"Of all Tel Aviv apartment deals in 2025, how many enter the price statistics, and why are the others excluded?","sql":"SELECT count(*) AS all_deals,\n       sum(in_stats) AS in_stats,\n       count(*) FILTER (WHERE is_full_deal = 0) AS partial_or_unknown_share,\n       sum(is_outlier) AS outliers,\n       sum(is_multi_unit) AS multi_unit,\n       sum(is_discount_project) AS discount_project,\n       count(*) FILTER (WHERE in_stats = 1 AND price_per_sqm IS NOT NULL) AS in_stats_with_ppsqm\nFROM deals\nWHERE settlement_code = 5000 AND property_group = 'apartment'\n  AND deal_date BETWEEN '2025-01-01' AND '2025-12-31'","explanation":"in_stats = 1 means not an outlier, not multi-unit, not a discount project, a full deal and at least ₪100k. The flags can overlap, so the exclusion columns do not add up exactly. count(*) FILTER (WHERE ...) is a compact conditional count.","tables":["deals"],"verified":{"rows":1,"ms":4.8,"at":"2026-09-28"}},{"id":83,"slug":"outlier-reasons","category":"data-quality","title_en":"Outlier reasons","title_he":"סיבות לסימון חריגים","question":"Which outlier rules fired most often in 2025?","sql":"SELECT outlier_reason, count(*) AS deals, min(deal_amount) AS min_amount, max(deal_amount) AS max_amount\nFROM deals\nWHERE deal_date BETWEEN '2025-01-01' AND '2025-12-31' AND is_outlier = 1\nGROUP BY outlier_reason\nORDER BY deals DESC","explanation":"outlier_reason is a comma-separated list of rule names. Price-level rules (ppsqm_iqr, price_iqr, ppsqm_out_of_band) still count in money volume, while amount-credibility rules (nominal_amount, declared_nominal, ...) do not.","tables":["deals"],"verified":{"rows":10,"ms":12,"at":"2026-09-28"}},{"id":84,"slug":"geo-precision-city","category":"data-quality","title_en":"Location precision of a city's deals","title_he":"דיוק המיקום של עסקאות העיר","question":"How precise are the map locations of Haifa's deals?","sql":"SELECT geo_precision, geo_source, count(*) AS parcels, sum(deals_total) AS deals,\n       round(100.0 * sum(deals_total) / (SELECT sum(deals_total) FROM parcels WHERE settlement_code = 4000), 1) AS pct_of_deals\nFROM parcels\nWHERE settlement_code = 4000\nGROUP BY geo_precision, geo_source\nORDER BY deals DESC","explanation":"geo_source = 'parcel' is an exact current-cadastre point. cancelled and shuma are approximate parcel points, gush is the block centroid, and settlement is the town centre (not a location). Aggregating parcels (weighted by deals_total) is much cheaper than scanning deals.","tables":["parcels"],"verified":{"rows":5,"ms":10,"at":"2026-09-28"}},{"id":85,"slug":"reporting-lag","category":"data-quality","title_en":"Reporting lag in recent months","title_he":"פיגור בדיווח בחודשים האחרונים","question":"Why do the statistics stop at June 2026? Show how complete each recent month is.","sql":"SELECT json_extract(j.value, '$.month') AS month,\n       json_extract(j.value, '$.residential') AS residential_deals,\n       json_extract(j.value, '$.same_month_prev_year') AS same_month_prev_year,\n       json_extract(j.value, '$.ratio') AS ratio\nFROM json_each((SELECT value FROM meta WHERE key = 'months_completeness')) AS j\nORDER BY month","explanation":"Deals are reported months late. The stats anchor is the last month whose residential count reached 85% of the same month a year earlier. Treat anything after it (is_incomplete = 1) as partial data, not a market drop.","tables":["meta"],"verified":{"rows":13,"ms":0,"at":"2026-09-28"}},{"id":86,"slug":"discount-flags-city","category":"data-quality","title_en":"Discount-project flags in a city","title_he":"סימוני פרויקטי הנחה בעיר","question":"How many Beer Sheva deals in the last 12 months were flagged as discount-lottery units, and on what basis?","sql":"SELECT coalesce(discount_source, 'not flagged') AS discount_source,\n       count(*) AS deals,\n       round(median(price_per_sqm)) AS median_ppsqm\nFROM deals\nWHERE settlement_code = 9000\n  AND property_group = 'apartment'\n  AND deal_date BETWEEN '2025-07-01' AND '2026-06-30'\n  AND is_full_deal = 1 AND is_outlier = 0 AND is_multi_unit = 0\nGROUP BY discount_source\nORDER BY deals DESC","explanation":"discount_source = 'official' means the deal was matched to an official lottery project, and 'heuristic' means it was flagged by the price rules only. Both are excluded from in_stats; show them with a \"reduced price\" badge.","tables":["deals"],"verified":{"rows":3,"ms":2.9,"at":"2026-09-28"}},{"id":87,"slug":"pooled-vs-existing-gap","category":"data-quality","title_en":"Where the headline median misleads","title_he":"היכן החציון הכללי מטעה","question":"In which cities does the headline median differ by more than 10% from the existing-stock median?","sql":"SELECT name, median_ppsqm_12m, n_ppsqm_12m, median_ppsqm_existing_12m, n_ppsqm_existing_12m,\n       median_ppsqm_new_12m, n_ppsqm_new_12m,\n       round(100.0 * median_ppsqm_12m / median_ppsqm_existing_12m - 100, 1) AS pooled_vs_existing_pct\nFROM settlements\nWHERE n_ppsqm_12m >= 30 AND n_ppsqm_existing_12m >= 20\n  AND abs(1.0 * median_ppsqm_12m / median_ppsqm_existing_12m - 1) > 0.10\nORDER BY pooled_vs_existing_pct","explanation":"median_ppsqm_12m pools new and existing stock. Where cheap new projects (not flagged as discount) or expensive towers dominate, it can differ a lot from the existing-stock level. Report both in that case.","tables":["settlements"],"verified":{"rows":16,"ms":0.1,"at":"2026-09-28"}},{"id":88,"slug":"partial-deals","category":"data-quality","title_en":"Partial-share deals","title_he":"עסקאות של חלק מנכס","question":"How common are partial-share sales in Tel Aviv in 2025, and at what shares?","sql":"SELECT CASE WHEN portion IS NULL THEN 'unknown'\n            WHEN portion = 1 THEN '1 (full)'\n            WHEN portion >= 0.5 THEN '0.5-0.99'\n            WHEN portion >= 0.25 THEN '0.25-0.49'\n            ELSE '< 0.25' END AS portion_band,\n       count(*) AS deals,\n       round(median(deal_amount)) AS median_amount\nFROM deals\nWHERE settlement_code = 5000 AND property_group = 'apartment'\n  AND deal_date BETWEEN '2025-01-01' AND '2025-12-31'\nGROUP BY portion_band\nORDER BY deals DESC","explanation":"deal_amount is the price of the sold share, so a partial deal's amount cannot be compared with full prices. A lone 50% row may still be a whole-unit sale by two equal co-owners.","tables":["deals"],"verified":{"rows":5,"ms":6.9,"at":"2026-09-28"}},{"id":89,"slug":"yoy-change-lag","category":"statistics","title_en":"Year-over-year change with LAG()","title_he":"שינוי שנתי עם LAG()","question":"What was the year-over-year change of Jerusalem's apartment price per m²?","sql":"SELECT year, n_ppsqm, median_ppsqm,\n       lag(median_ppsqm) OVER (ORDER BY year) AS prev_year,\n       round(100.0 * median_ppsqm / lag(median_ppsqm) OVER (ORDER BY year) - 100, 1) AS yoy_pct,\n       is_incomplete\nFROM agg_settlement_year\nWHERE settlement_code = 3000 AND property_group = 'apartment'\nORDER BY year","explanation":"lag() reads the previous row of the ordered window. The yearly median pools new and existing stock, so a year with an unusual share of new projects can move it; the official change columns use existing stock only.","tables":["agg_settlement_year"],"verified":{"rows":29,"ms":0.1,"at":"2026-09-28"}},{"id":90,"slug":"top-per-district","category":"statistics","title_en":"Top 3 cities per district with RANK()","title_he":"שלוש הערים המובילות בכל מחוז עם RANK()","question":"What are the three most expensive cities in each district?","sql":"SELECT district, rnk, name, median_ppsqm_12m, n_ppsqm_12m\nFROM (\n  SELECT district, name, median_ppsqm_12m, n_ppsqm_12m,\n         rank() OVER (PARTITION BY district ORDER BY median_ppsqm_12m DESC) AS rnk\n  FROM settlements\n  WHERE n_ppsqm_12m >= 30 AND district IS NOT NULL\n)\nWHERE rnk <= 3\nORDER BY district, rnk","explanation":"rank() OVER (PARTITION BY ...) numbers rows within each group, and filtering rnk <= 3 in an outer query gives the top-N per group. Apply the n gate before ranking.","tables":["settlements"],"verified":{"rows":18,"ms":0.4,"at":"2026-09-28"}},{"id":91,"slug":"moving-average","category":"statistics","title_en":"Moving average of a quarterly series","title_he":"ממוצע נע של סדרה רבעונית","question":"Smooth the national quarterly apartment price per m² with a 4-quarter moving average.","sql":"SELECT quarter, median_ppsqm,\n       round(avg(median_ppsqm) OVER (ORDER BY quarter ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)) AS ma_4q,\n       is_incomplete\nFROM agg_national_quarter\nWHERE property_group = 'apartment' AND rooms_bucket = 'all' AND quarter >= '2015-Q1'\nORDER BY quarter","explanation":"A ROWS BETWEEN 3 PRECEDING AND CURRENT ROW frame averages each quarter with the 3 before it. This smooths a displayed trend line; it is not a way to combine medians into a new statistic.","tables":["agg_national_quarter"],"verified":{"rows":47,"ms":0.1,"at":"2026-09-28"}},{"id":92,"slug":"percentiles","category":"statistics","title_en":"Price percentiles in a city","title_he":"אחוזוני מחיר בעיר","question":"What is the price distribution (10th to 90th percentile) of apartments sold in Rishon LeZion in the last 12 months?","sql":"SELECT count(*) AS n,\n       round(percentile(deal_amount, 10)) AS p10,\n       round(percentile(deal_amount, 25)) AS p25,\n       round(median(deal_amount)) AS p50,\n       round(percentile(deal_amount, 75)) AS p75,\n       round(percentile(deal_amount, 90)) AS p90,\n       round(median(price_per_sqm)) AS median_ppsqm\nFROM deals\nWHERE settlement_code = 8300 AND property_group = 'apartment'\n  AND deal_date BETWEEN '2025-07-01' AND '2026-06-30'\n  AND in_stats = 1","explanation":"This SQLite build has the percentile extension: median(x), percentile(x, p) with p from 0 to 100, and percentile_cont(x, f) with f from 0 to 1. median() averages the two middle values when n is even; round() it to match the precomputed integer medians.","tables":["deals"],"verified":{"rows":1,"ms":1.8,"at":"2026-09-28"}},{"id":93,"slug":"median-window-idiom","category":"statistics","title_en":"Portable median with window functions","title_he":"חציון נייד באמצעות פונקציות חלון","question":"How do I compute a median without a median() function, e.g. Tel Aviv apartments in 2025?","sql":"WITH ranked AS (\n  SELECT price_per_sqm AS v,\n         row_number() OVER (ORDER BY price_per_sqm) AS rn,\n         count(*) OVER () AS n\n  FROM deals\n  WHERE settlement_code = 5000 AND property_group = 'apartment'\n    AND deal_date BETWEEN '2025-01-01' AND '2025-12-31'\n    AND in_stats = 1 AND price_per_sqm IS NOT NULL\n)\nSELECT max(n) AS n, round(avg(v)) AS median_ppsqm\nFROM ranked\nWHERE rn IN ((n + 1) / 2, (n + 2) / 2)","explanation":"Number the sorted values, then average the middle one (odd n) or the two middle ones (even n). Integer division picks the right positions. The result, 56,707, equals agg_settlement_year.median_ppsqm and median().","tables":["deals"],"verified":{"rows":1,"ms":7.2,"at":"2026-09-28"}},{"id":94,"slug":"ppsqm-histogram","category":"statistics","title_en":"Histogram of price per m²","title_he":"היסטוגרמה של מחיר למ״ר","question":"What is the distribution of price per m² for apartments sold in Haifa in the last 12 months, in 2,500 ILS bins?","sql":"SELECT (price_per_sqm / 2500) * 2500 AS bin_from,\n       (price_per_sqm / 2500) * 2500 + 2499 AS bin_to,\n       count(*) AS deals\nFROM deals\nWHERE settlement_code = 4000 AND property_group = 'apartment'\n  AND deal_date BETWEEN '2025-07-01' AND '2026-06-30'\n  AND in_stats = 1 AND price_per_sqm IS NOT NULL\nGROUP BY bin_from\nORDER BY bin_from","explanation":"price_per_sqm is an INTEGER, so (x / 2500) * 2500 floors it to its bin. Bins with no deals are absent; fill them on the client if you chart the result.","tables":["deals"],"verified":{"rows":20,"ms":2.7,"at":"2026-09-28"}},{"id":95,"slug":"busiest-days","category":"statistics","title_en":"Busiest single days on record","title_he":"הימים העמוסים ביותר","question":"On which single days were the most deals recorded from December 2013 to 2025?","sql":"SELECT deal_date, count(*) AS deals,\n       sum(CASE WHEN property_group = 'apartment' THEN 1 ELSE 0 END) AS apartments\nFROM deals\nWHERE deal_date BETWEEN '2013-12-01' AND '2025-12-31'\nGROUP BY deal_date\nORDER BY deals DESC\nLIMIT 10","explanation":"This scans about 1.5M entries, but only inside the covering index ix_deals_date (which also carries property_group), so it stays fast. Spikes line up with tax changes, such as 2015-06-23 before the purchase-tax hike and 2013-12-31 before the מס שבח reform.","tables":["deals"],"verified":{"rows":10,"ms":228,"at":"2026-09-28"}},{"id":96,"slug":"recipe-rehavia-4-rooms-2025","category":"recipes","title_en":"Recipe: 4-room apartments in Rehavia in 2025","title_he":"מתכון: דירות 4 חדרים ברחביה ב-2025","question":"How much did 4-room apartments cost in Rehavia (Jerusalem) in 2025?","sql":"SELECT n.name AS neighborhood,\n       count(*) AS all_deals,\n       sum(d.in_stats) AS n_stats,\n       CAST(round(median(CASE WHEN d.in_stats = 1 THEN d.deal_amount END)) AS INTEGER) AS median_price,\n       CAST(round(median(CASE WHEN d.in_stats = 1 THEN d.price_per_sqm END)) AS INTEGER) AS median_ppsqm,\n       min(CASE WHEN d.in_stats = 1 THEN d.deal_amount END) AS min_price,\n       max(CASE WHEN d.in_stats = 1 THEN d.deal_amount END) AS max_price\nFROM neighborhoods n\nCROSS JOIN parcel_neighborhood pn ON pn.nbhd_id = n.nbhd_id\nCROSS JOIN deals d ON d.gush = pn.gush AND d.chelka = pn.chelka\nWHERE n.settlement_code = 3000 AND n.name = 'רחביה'\n  AND d.settlement_code = n.settlement_code\n  AND d.property_group = 'apartment'\n  AND d.rooms >= 4 AND d.rooms < 5\n  AND d.deal_date BETWEEN '2025-01-01' AND '2025-12-31'\nGROUP BY n.name","explanation":"Resolve the city (settlements) and the neighbourhood (neighborhoods or neighborhoods_fts), walk its parcels, and take median() over in_stats deals only. Report n_stats next to the median: with about 25 deals, this is a small sample.","tables":["neighborhoods","parcel_neighborhood","deals"],"verified":{"rows":1,"ms":0.3,"at":"2026-09-28"}},{"id":97,"slug":"recipe-city-rooms-trend","category":"recipes","title_en":"Recipe: 3-room apartment prices in a city, recent quarters","title_he":"מתכון: מחירי דירות 3 חדרים בעיר ברבעונים האחרונים","question":"What does a 3-room apartment cost in Haifa lately, and is it going up?","sql":"SELECT quarter, deals, n_stats, median_price, median_ppsqm, median_area, is_incomplete\nFROM agg_settlement_quarter\nWHERE settlement_code = (SELECT code FROM settlements WHERE name = 'חיפה')\n  AND property_group = 'apartment' AND rooms_bucket = '3'\n  AND quarter >= '2023-Q1'\nORDER BY quarter","explanation":"For \"how much and which direction\" questions, read the precomputed quarterly medians for the right rooms_bucket, and resolve the city code with a subquery. Judge the direction on complete quarters (is_incomplete = 0) with a reasonable n_stats.","tables":["agg_settlement_quarter","settlements"],"verified":{"rows":15,"ms":0.1,"at":"2026-09-28"}},{"id":98,"slug":"recipe-compare-two-cities","category":"recipes","title_en":"Recipe: compare two neighbouring cities","title_he":"מתכון: השוואה בין שתי ערים שכנות","question":"Should I buy in Ramat Gan or Givatayim? Compare prices, change, 4-room prices and rental yield.","sql":"SELECT s.name, s.median_ppsqm_12m, s.n_ppsqm_12m, s.median_ppsqm_existing_12m, s.ppsqm_change_pct,\n       s.median_price_4rooms_12m, s.n_4rooms_12m, s.deals_12m,\n       ss.cluster AS socio_cluster, gy.gross_yield_pct\nFROM settlements s\nLEFT JOIN settlement_socio ss ON ss.settlement_code = s.code\nLEFT JOIN gross_yield gy ON gy.settlement_code = s.code AND gy.level = 'city' AND gy.window = '12m' AND gy.rooms_bucket = 'all'\nWHERE s.code IN (8600, 6300)","explanation":"One row per city with the precomputed 12-month numbers plus enrichment (socio-economic cluster, gross yield). Yield exists only for the 18 big cities, so it is NULL for Givatayim; say so rather than guessing.","tables":["settlements","settlement_socio","gross_yield"],"verified":{"rows":2,"ms":0,"at":"2026-09-28"}},{"id":99,"slug":"recipe-real-5y-change","category":"recipes","title_en":"Recipe: real price change over 5 years","title_he":"מתכון: שינוי ריאלי במחיר ב-5 שנים","question":"Are Jerusalem apartments more expensive now than 5 years ago after inflation?","sql":"WITH y AS (\n  SELECT a.year, a.median_ppsqm, a.n_ppsqm,\n         round(a.median_ppsqm * (SELECT avg(m.real_factor) FROM macro_month m WHERE m.month BETWEEN a.year || '-01' AND a.year || '-12')) AS median_ppsqm_real\n  FROM agg_settlement_year a\n  WHERE a.settlement_code = 3000 AND a.property_group = 'apartment' AND a.year IN (2020, 2025)\n)\nSELECT y20.median_ppsqm AS ppsqm_2020, y25.median_ppsqm AS ppsqm_2025,\n       round(100.0 * y25.median_ppsqm / y20.median_ppsqm - 100, 1) AS nominal_change_pct,\n       y20.median_ppsqm_real AS ppsqm_2020_real, y25.median_ppsqm_real AS ppsqm_2025_real,\n       round(100.0 * y25.median_ppsqm_real / y20.median_ppsqm_real - 100, 1) AS real_change_pct\nFROM y y20 JOIN y y25 ON y20.year = 2020 AND y25.year = 2025","explanation":"Compare complete years only (2025, not the partial 2026) and convert each to June-2026 shekels with the year's mean real_factor. The real change is the nominal change with CPI inflation removed.","tables":["macro_month","agg_settlement_year"],"verified":{"rows":1,"ms":0,"at":"2026-09-28"}},{"id":100,"slug":"recipe-budget-4-rooms","category":"recipes","title_en":"Recipe: where can I afford a 4-room apartment?","title_he":"מתכון: היכן אפשר לקנות דירת 4 חדרים בתקציב?","question":"In which cities can I buy a typical 4-room apartment for up to 2 million ILS?","sql":"SELECT name, district, median_price_4rooms_12m, n_4rooms_12m, median_ppsqm_12m, population\nFROM settlements\nWHERE median_price_4rooms_12m <= 2000000 AND n_4rooms_12m >= 30\nORDER BY median_price_4rooms_12m DESC\nLIMIT 25","explanation":"median_price_4rooms_12m is the median of 4-4.5-room apartments over the 12 months to the anchor (in_stats). Sorting by price descending shows the most expensive places that still fit the budget first; the n >= 30 gate drops thin samples.","tables":["settlements"],"verified":{"rows":25,"ms":0.1,"at":"2026-09-28"}}]